Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

insert into nested structures

insert into nested structures

New Contributor

Cree la siguiente tabla donde están cargados los datos que insertare en mi tabla de estructura compleja. Me genera un error.

¿cual seria la forma correcta?

Tabla de datos.

CREATE TABLE BDW.example_bandera (continent STRING,
country STRING,
city STRING,
moneda STRING,
valor decimal(12,2),
abv STRING,
bandera STRING
);

 

Datos

 

 

INSERT INTO BDW.example_bandera
VALUES
('North America', 'Canada', 'Toronto', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Vancouver', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', "St. John\'s", 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Saint John', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Montreal', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Halifax', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Winnipeg', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Calgary', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Saskatoon', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Ottawa', 'dolar',17.52,'usd','ROJO')
,('North America', 'Canada', 'Yellowknife', 'dolar',17.52,'usd','ROJO')

--- Create table struct

 

CREATE TABLE BDW.STRUCT_EXAMPLE (
continent STRING,
  country STRUCT < name: STRING,
                                    city: ARRAY <STRING> ,
                                    moneda: STRUCT < name: STRING,  

                                                                      valor: DECIMAL(12,2),
                                                                      abv: ARRAY <STRING>
                            >
                     >,
bandera STRING
)
STORED AS PARQUET;

 

 

Insert

 

INSERT INTO BDW.STRUCT_EXAMPLE
SELECT continent,
named_struct('name', country,
'city', collect_list(city),
named_struct('name' , moneda,
'valor', collect_list(valor),
'abv' , collect_list(abv)
)
),
bandera
FROM BDW.example_bandera
GROUP BY continent,
country,
moneda,
bandera;

 

Error 

 

Error while compiling statement: FAILED: SemanticException [Error 10015]: line 15:9 Arguments length mismatch 'abv': NAMED_STRUCT expects an even number of arguments.

Don't have an account?
Coming from Hortonworks? Activate your account here