Found the same issue is raised for clarification which can be found here #880 and #882 And i tried the same things suggested.but it was not working.
My Error Message:-
2024-03-24T18:28:35.048Z DEFAULT Caused by: com.google.cloud.spark.bigquery.repackaged.com.google.cloud.bigquery.BigQueryException: Provided Schema does not match Table pypl-bods:prd_pdh_everett.card_transaction_settlement_test. Field id has changed type from JSON to STRING
Table i have created for testing:-
CREATE TABLE $tablename
(
id JSON,
source JSON
)
Maven Dependency i have configured in pom:-
<dependency>
<groupId>com.google.cloud.spark</groupId>
<artifactId>spark-bigquery-with-dependencies_2.12</artifactId>
<version>0.34.0</version>
</dependency>
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-avro_2.12</artifactId>
<version>3.3.2</version>
</dependency>
Method for writing the data:-
def writeDatasetAvro(table: Table, ds: Dataset[Row], spark: SparkSession, env: Env ): Unit = {
ds.write.format("bigquery")
.mode(SaveMode.Append)
.option("writeMethod", "indirect")
.option("intermediateFormat", "avro")
.option("table", resolveTableName(table, env))
.option("project", projectId(table.dataSource).get)
.option("parentProject", spark.conf.get("parentProjectId"))
//.option("credentials",getEncodedCredentials("/resources/gcp_config/recon.json"))
.save()
}
Scala code:-
val table_schema = new StructType()
.add(StructField("id", StringType, true, Metadata.fromJson("{\"sqlType\":\"JSON\"}")))
.add(StructField("source", StringType, true, Metadata.fromJson("{\"sqlType\":\"JSON\"}")))
val RDD = etlPipelineContext.getSparkSession().sparkContext.parallelize(
Seq(
("""["OPCNpoQB_z8XYSv3AfQC"]""", """{"category":["Men's Clothing"],"currency":"EUR","customer_first_name":"Eddie","customer_full_name":"Eddie Underwood","customer_gender":"MALE","customer_id":38,"customer_last_name":"Underwood","customer_phone":"","day_of_week":"Monday","day_of_week_i":0,"email":"[email protected]","manufacturer":["Elitelligence","Oceanavigations"],"order_date":"2022-12-05T09:28:48+00:00","order_id":584677,"products":[{"base_price":11.99,"discount_percentage":0,"quantity":1,"manufacturer":"Elitelligence","tax_amount":0,"product_id":6283,"category":"Men's Clothing","sku":"ZO0549605496","taxless_price":11.99,"unit_discount_amount":0,"min_price":6.35,"_id":"sold_product_584677_6283","discount_amount":0,"created_on":"2016-12-26T09:28:48+00:00","product_name":"Basic T-shirt - dark blue/white","price":11.99,"taxful_price":11.99,"base_unit_price":11.99},{"base_price":24.99,"discount_percentage":0,"quantity":1,"manufacturer":"Oceanavigations","tax_amount":0,"product_id":19400,"category":"Men's Clothing","sku":"ZO0299602996","taxless_price":24.99,"unit_discount_amount":0,"min_price":11.75,"_id":"sold_product_584677_19400","discount_amount":0,"created_on":"2016-12-26T09:28:48+00:00","product_name":"Sweatshirt - grey multicolor","price":24.99,"taxful_price":24.99,"base_unit_price":24.99}],"sku":["ZO0549605496","ZO0299602996"],"taxful_total_price":36.98,"taxless_total_price":36.98,"total_quantity":2,"total_unique_products":2,"type":"order","user":"eddie","geoip":{"country_iso_code":"EG","location":{"lon":31.3,"lat":30.1},"region_name":"Cairo Governorate","continent_name":"Africa","city_name":"Cairo"},"event":{"dataset":"sample_ecommerce"},"_metadata":{"_index":"kibana_sample_data_ecommerce","_type":"_doc","_id":"OPCNpoQB_z8XYSv3AfQC","_score":1.0,"sort":[0]}}"""),
("""["OfCNpoQB_z8XYSv3AfQC"]""", """{"category":["Women's Clothing"],"currency":"EUR","customer_first_name":"Mary","customer_full_name":"Mary Bailey","customer_gender":"FEMALE","customer_id":20,"customer_last_name":"Bailey","customer_phone":"","day_of_week":"Sunday","day_of_week_i":6,"email":"[email protected]","manufacturer":["Champion Arts","Pyramidustries"],"order_date":"2022-12-04T21:59:02+00:00","order_id":584021,"products":[{"base_price":24.99,"discount_percentage":0,"quantity":1,"manufacturer":"Champion Arts","tax_amount":0,"product_id":11238,"category":"Women's Clothing","sku":"ZO0489604896","taxless_price":24.99,"unit_discount_amount":0,"min_price":11.75,"_id":"sold_product_584021_11238","discount_amount":0,"created_on":"2016-12-25T21:59:02+00:00","product_name":"Denim dress - black denim","price":24.99,"taxful_price":24.99,"base_unit_price":24.99},{"base_price":28.99,"discount_percentage":0,"quantity":1,"manufacturer":"Pyramidustries","tax_amount":0,"product_id":20149,"category":"Women's Clothing","sku":"ZO0185501855","taxless_price":28.99,"unit_discount_amount":0,"min_price":15.65,"_id":"sold_product_584021_20149","discount_amount":0,"created_on":"2016-12-25T21:59:02+00:00","product_name":"Shorts - black","price":28.99,"taxful_price":28.99,"base_unit_price":28.99}],"sku":["ZO0489604896","ZO0185501855"],"taxful_total_price":53.98,"taxless_total_price":53.98,"total_quantity":2,"total_unique_products":2,"type":"order","user":"mary","geoip":{"country_iso_code":"AE","location":{"lon":55.3,"lat":25.3},"region_name":"Dubai","continent_name":"Asia","city_name":"Dubai"},"event":{"dataset":"sample_ecommerce"},"_metadata":{"_index":"kibana_sample_data_ecommerce","_type":"_doc","_id":"OfCNpoQB_z8XYSv3AfQC","_score":1.0,"sort":[1]}}"""),
("""[""OvCNpoQB_z8XYSv3AfQC"]""", """{"category":["Women's Shoes","Women's Clothing"],"currency":"EUR","customer_first_name":"Gwen","customer_full_name":"Gwen Butler","customer_gender":"FEMALE","customer_id":26,"customer_last_name":"Butler","customer_phone":"","day_of_week":"Sunday","day_of_week_i":6,"email":"[email protected]","manufacturer":["Low Tide Media","Oceanavigations"],"order_date":"2022-12-04T22:32:10+00:00","order_id":584058,"products":[{"base_price":99.99,"discount_percentage":0,"quantity":1,"manufacturer":"Low Tide Media","tax_amount":0,"product_id":22794,"category":"Women's Shoes","sku":"ZO0374603746","taxless_price":99.99,"unit_discount_amount":0,"min_price":46.01,"_id":"sold_product_584058_22794","discount_amount":0,"created_on":"2016-12-25T22:32:10+00:00","product_name":"Boots - Midnight Blue","price":99.99,"taxful_price":99.99,"base_unit_price":99.99},{"base_price":99.99,"discount_percentage":0,"quantity":1,"manufacturer":"Oceanavigations","tax_amount":0,"product_id":23386,"category":"Women's Clothing","sku":"ZO0272202722","taxless_price":99.99,"unit_discount_amount":0,"min_price":53.99,"_id":"sold_product_584058_23386","discount_amount":0,"created_on":"2016-12-25T22:32:10+00:00","product_name":"Short coat - white/black","price":99.99,"taxful_price":99.99,"base_unit_price":99.99}],"sku":["ZO0374603746","ZO0272202722"],"taxful_total_price":199.98,"taxless_total_price":199.98,"total_quantity":2,"total_unique_products":2,"type":"order","user":"gwen","geoip":{"country_iso_code":"US","location":{"lon":-118.2,"lat":34.1},"region_name":"California","continent_name":"North America","city_name":"Los Angeles"},"event":{"dataset":"sample_ecommerce"},"_metadata":{"_index":"kibana_sample_data_ecommerce","_type":"_doc","_id":"OvCNpoQB_z8XYSv3AfQC","_score":1.0,"sort":[2]}}"""),
("""["O_CNpoQB_z8XYSv3AfQC"]""", """{"category":["Women's Shoes","Women's Clothing"],"currency":"EUR","customer_first_name":"Diane","customer_full_name":"Diane Chandler","customer_gender":"FEMALE","customer_id":22,"customer_last_name":"Chandler","customer_phone":"","day_of_week":"Sunday","day_of_week_i":6,"email":"[email protected]","manufacturer":["Primemaster","Oceanavigations"],"order_date":"2022-12-04T22:58:05+00:00","order_id":584093,"products":[{"base_price":74.99,"discount_percentage":0,"quantity":1,"manufacturer":"Primemaster","tax_amount":0,"product_id":12304,"category":"Women's Shoes","sku":"ZO0360303603","taxless_price":74.99,"unit_discount_amount":0,"min_price":34.5,"_id":"sold_product_584093_12304","discount_amount":0,"created_on":"2016-12-25T22:58:05+00:00","product_name":"High heeled sandals - argento","price":74.99,"taxful_price":74.99,"base_unit_price":74.99},{"base_price":99.99,"discount_percentage":0,"quantity":1,"manufacturer":"Oceanavigations","tax_amount":0,"product_id":19587,"category":"Women's Clothing","sku":"ZO0272002720","taxless_price":99.99,"unit_discount_amount":0,"min_price":47.01,"_id":"sold_product_584093_19587","discount_amount":0,"created_on":"2016-12-25T22:58:05+00:00","product_name":"Classic coat - black","price":99.99,"taxful_price":99.99,"base_unit_price":99.99}],"sku":["ZO0360303603","ZO0272002720"],"taxful_total_price":174.98,"taxless_total_price":174.98,"total_quantity":2,"total_unique_products":2,"type":"order","user":"diane","geoip":{"country_iso_code":"GB","location":{"lon":-0.1,"lat":51.5},"continent_name":"Europe"},"event":{"dataset":"sample_ecommerce"},"_metadata":{"_index":"kibana_sample_data_ecommerce","_type":"_doc","_id":"O_CNpoQB_z8XYSv3AfQC","_score":1.0,"sort":[3]}}"""),
("""["PPCNpoQB_z8XYSv3AfQC"]""", """{"category":["Men's Clothing","Men's Accessories"],"currency":"EUR","customer_first_name":"Eddie","customer_full_name":"Eddie Weber","customer_gender":"MALE","customer_id":38,"customer_last_name":"Weber","customer_phone":"","day_of_week":"Monday","day_of_week_i":0,"email":"[email protected]","manufacturer":["Elitelligence"],"order_date":"2022-11-28T03:48:58+00:00","order_id":574916,"products":[{"base_price":59.99,"discount_percentage":0,"quantity":1,"manufacturer":"Elitelligence","tax_amount":0,"product_id":11262,"category":"Men's Clothing","sku":"ZO0542505425","taxless_price":59.99,"unit_discount_amount":0,"min_price":28.2,"_id":"sold_product_574916_11262","discount_amount":0,"created_on":"2016-12-19T03:48:58+00:00","product_name":"Winter jacket - black","price":59.99,"taxful_price":59.99,"base_unit_price":59.99},{"base_price":20.99,"discount_percentage":0,"quantity":1,"manufacturer":"Elitelligence","tax_amount":0,"product_id":15713,"category":"Men's Accessories","sku":"ZO0601306013","taxless_price":20.99,"unit_discount_amount":0,"min_price":10.7,"_id":"sold_product_574916_15713","discount_amount":0,"created_on":"2016-12-19T03:48:58+00:00","product_name":"Watch - green","price":20.99,"taxful_price":20.99,"base_unit_price":20.99}],"sku":["ZO0542505425","ZO0601306013"],"taxful_total_price":80.98,"taxless_total_price":80.98,"total_quantity":2,"total_unique_products":2,"type":"order","user":"eddie","geoip":{"country_iso_code":"EG","location":{"lon":31.3,"lat":30.1},"region_name":"Cairo Governorate","continent_name":"Africa","city_name":"Cairo"},"event":{"dataset":"sample_ecommerce"},"_metadata":{"_index":"kibana_sample_data_ecommerce","_type":"_doc","_id":"PPCNpoQB_z8XYSv3AfQC","_score":1.0,"sort":[4]}}""")
)
)
val rowRDD = RDD.map(t => Row(t._1, t._2))
val df = etlPipelineContext.getSparkSession().createDataFrame(rowRDD, table_schema)