66315674

Date: 2021-02-22 12:29:56
Score: 9.5
Natty:
Report link

I have the same problem. I have checked the answer Hive select data into an array of structs and it helped me, but not for inserting the result into a struct field.

My table is as follows:

CREATE EXTERNAL TABLE IF NOT EXISTS schema.test_json_struc( date string, big_arr struct<region:string, metrics:struct<nb_cust:bigint, nb_failed:bigint, nb_failed_mtd:bigint>> ) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE LOCATION '/user/path/test' ;

Here is my query to insert in this table: WITH metrics as ( select ebb_date_ref as kpi_date ,'region' as key_type ,knb_do as key_value ,named_struct('nb_cust',nvl(nb_cli, 0),'nb_failed',nvl(nb_deco, 0),'nb_failed_mtd',nvl(nb_deco_mtd, 0)) as met from z_lab_kci_hive_temp.test_imo_deco_mtd ) INSERT OVERWRITE TABLE schema.test_json_struc select kpi_date, collect(named_struct('region',key_value,'metrics',met)) as arr from metrics group by kpi_date ;

The data looks like

2021-02-15 [{"region":"ILE-DE-FRANCE","metrics":{"nb_cust":1700247,"nb_failed":335134,"nb_failed_mtd":4269474}},{"region":"GRAND NORD-EST","metrics":{"nb_cust":2398552,"nb_failed":573265,"nb_failed_mtd":7355755}},{"region":"CARAIBES","metrics":{"nb_cust":208024,"nb_failed":113955,"nb_failed_mtd":1853619}},{"region":"inconnu","metrics":{"nb_cust":235178,"nb_failed":68557,"nb_failed_mtd":966470}},{"region":"Inconnu","metrics":{"nb_cust":8931,"nb_failed":935,"nb_failed_mtd":14830}},{"region":"GRAND SUD-EST","metrics":{"nb_cust":2494355,"nb_failed":639999,"nb_failed_mtd":8319161}},{"region":"GRAND SUD-OUEST","metrics":{"nb_cust":2575812,"nb_failed":709161,"nb_failed_mtd":9737428}},{"region":"GRAND OUEST","metrics":{"nb_cust":2766574,"nb_failed":763354,"nb_failed_mtd":10460136}},{"region":"REUNION MAYOTTE","metrics":{"nb_cust":138769,"nb_failed":48416,"nb_failed_mtd":578561}},{"region":"ETRANGER","metrics":{"nb_cust":319,"nb_failed":23,"nb_failed_mtd":302}}] 2021-02-16 [{"region":"Inconnu","metrics":{"nb_cust":8161,"nb_failed":1121,"nb_failed_mtd":15944}},{"region":"ILE-DE-FRANCE","metrics":{"nb_cust":1700672,"nb_failed":370729,"nb_failed_mtd":4633723}},{"region":"GRAND OUEST","metrics":{"nb_cust":2767117,"nb_failed":843112,"nb_failed_mtd":11295617}},{"region":"ETRANGER","metrics":{"nb_cust":319,"nb_failed":14,"nb_failed_mtd":316}},{"region":"GRAND SUD-EST","metrics":{"nb_cust":2494866,"nb_failed":652704,"nb_failed_mtd":8965971}},{"region":"CARAIBES","metrics":{"nb_cust":207950,"nb_failed":103196,"nb_failed_mtd":1956478}},{"region":"GRAND SUD-OUEST","metrics":{"nb_cust":2576509,"nb_failed":726876,"nb_failed_mtd":10452972}},{"region":"REUNION MAYOTTE","metrics":{"nb_cust":138762,"nb_failed":52434,"nb_failed_mtd":630179}},{"region":"inconnu","metrics":{"nb_cust":238225,"nb_failed":70366,"nb_failed_mtd":1060184}},{"region":"GRAND NORD-EST","metrics":{"nb_cust":2399276,"nb_failed":686212,"nb_failed_mtd":8033212}}] 2021-02-17 [{"region":"Inconnu","metrics":{"nb_cust":8160,"nb_failed":1112,"nb_failed_mtd":17053}},{"region":"GRAND NORD-EST","metrics":{"nb_cust":2396833,"nb_failed":555055,"nb_failed_mtd":8576463}},{"region":"ETRANGER","metrics":{"nb_cust":317,"nb_failed":19,"nb_failed_mtd":333}},{"region":"inconnu","metrics":{"nb_cust":241475,"nb_failed":71582,"nb_failed_mtd":1160063}},{"region":"GRAND SUD-OUEST","metrics":{"nb_cust":2574720,"nb_failed":746116,"nb_failed_mtd":11190038}},{"region":"REUNION MAYOTTE","metrics":{"nb_cust":138726,"nb_failed":50620,"nb_failed_mtd":680572}},{"region":"ILE-DE-FRANCE","metrics":{"nb_cust":1699511,"nb_failed":369352,"nb_failed_mtd":4997626}},{"region":"GRAND SUD-EST","metrics":{"nb_cust":2492838,"nb_failed":644073,"nb_failed_mtd":9591742}},{"region":"CARAIBES","metrics":{"nb_cust":207907,"nb_failed":114328,"nb_failed_mtd":2070387}},{"region":"GRAND OUEST","metrics":{"nb_cust":2765407,"nb_failed":799649,"nb_failed_mtd":12083407}}] 2021-02-18 [{"region":"Inconnu","metrics":{"nb_cust":8160,"nb_failed":1038,"nb_failed_mtd":18091}},{"region":"GRAND NORD-EST","metrics":{"nb_cust":2396155,"nb_failed":500133,"nb_failed_mtd":9074495}},{"region":"CARAIBES","metrics":{"nb_cust":207904,"nb_failed":264667,"nb_failed_mtd":2335053}},{"region":"ETRANGER","metrics":{"nb_cust":317,"nb_failed":22,"nb_failed_mtd":336}},{"region":"GRAND SUD-OUEST","metrics":{"nb_cust":2574160,"nb_failed":626171,"nb_failed_mtd":11813591}},{"region":"GRAND OUEST","metrics":{"nb_cust":2764863,"nb_failed":711193,"nb_failed_mtd":12792386}},{"region":"REUNION MAYOTTE","metrics":{"nb_cust":138718,"nb_failed":61896,"nb_failed_mtd":742456}},{"region":"inconnu","metrics":{"nb_cust":237384,"nb_failed":64318,"nb_failed_mtd":1200050}},{"region":"GRAND SUD-EST","metrics":{"nb_cust":2492389,"nb_failed":555451,"nb_failed_mtd":10146089}},{"region":"ILE-DE-FRANCE","metrics":{"nb_cust":1699296,"nb_failed":293532,"nb_failed_mtd":5290405}}] 2021-02-19 [{"region":"Inconnu","metrics":{"nb_cust":8160,"nb_failed":20,"nb_failed_mtd":18111}},{"region":"CARAIBES","metrics":{"nb_cust":207360,"nb_failed":4293,"nb_failed_mtd":2338568}},{"region":"GRAND OUEST","metrics":{"nb_cust":2756171,"nb_failed":15375,"nb_failed_mtd":12800501}},{"region":"GRAND NORD-EST","metrics":{"nb_cust":2385021,"nb_failed":10929,"nb_failed_mtd":9076319}},{"region":"GRAND SUD-EST","metrics":{"nb_cust":2483921,"nb_failed":12527,"nb_failed_mtd":10152609}},{"region":"ILE-DE-FRANCE","metrics":{"nb_cust":1694211,"nb_failed":7361,"nb_failed_mtd":5293944}},{"region":"inconnu","metrics":{"nb_cust":194965,"nb_failed":1099,"nb_failed_mtd":1022143}},{"region":"REUNION MAYOTTE","metrics":{"nb_cust":138289,"nb_failed":709,"nb_failed_mtd":742792}},{"region":"ETRANGER","metrics":{"nb_cust":318,"nb_failed":0,"nb_failed_mtd":357}},{"region":"GRAND SUD-OUEST","metrics":{"nb_cust":2565484,"nb_failed":16144,"nb_failed_mtd":11820504}}]

The error is:[Code: 10044, SQL State: 42000] Error while compiling statement: FAILED: SemanticException [Error 10044]: Line 9:23 Cannot insert into target table because column number/types are different 'test_json_struc': Cannot convert column 1 from array<structregion:string,metrics:struct<nb_cust:bigint,nb_failed:bigint,nb_failed_mtd:bigint>> to structregion:string,metrics:struct<nb_cust:bigint,nb_failed:bigint,nb_failed_mtd:bigint>.

I can't find a way, any help will be appreciated.

Ivan

Reasons:
  • Blacklisted phrase (1): appreciated
  • Blacklisted phrase (1): have the same problem
  • Blacklisted phrase (1): any help
  • RegEx Blacklisted phrase (3): any help will be appreciated
  • Contains signature (1):
  • Long answer (-1):
  • No code block (0.5):
  • Me too answer (2): I have the same problem
  • Low reputation (1):
Posted by: Ivan