How can I load Hive table with complex data types using INSERT-SELECT query

Question

We have a query for loading the data into a table using a INSERT-SELECT query directly on another table(s) as shown below

INSERT OVERWRITE TABLE <table1>
SELECT * FROM <table2> t2
WHERE <some-conditions>;

Similarly how can load a table with complex data types? How can I let couple of/some of columns in my SELECT query contribute for a complex data typed column? Am i clear?

Schema of the table1 is

TABLE (col1 INT, col2 STRING, col3 ARRAY<STRING>)

Note: Loading from a file to such tables is possible, but I just wanna try whether I can load using above INSERT-SELECT query fashion. Appreciate your interest.

what is the schema of the table where the data is coming from? e.g. t2 in your example? does it match the schema of the table being loaded, can it be converted/casted? — libjack
– libjack, Commented Nov 6, 2013 at 16:46
Going one by one of your questions, Schema given above is for table1 only. Now I may get the data as a text file to be loaded into table1, which is very well possible and feasible as well. If I have a simple SELECT query or a JOIN query which takes couple of columns from table2 (or from couple of tables using JOIN) in order to load the data into tabel1, how can i make sure that the data being pulled suits the schema? Am I clear? Thats where I need the syntax of the INSERT-SELECT query. — Raja Reddy
– Raja Reddy, Commented Nov 6, 2013 at 18:24
how can i make sure that the data being pulled suits the schema? -- if you know the destination schema, then you can chose what to select and convert if necessary to new schema -- using CAST of UDFs which generate new types of data, e.g. SPLIT will create array<string> from string. — libjack
– libjack, Commented Nov 8, 2013 at 15:24

WestCoastProjects · Accepted Answer · 2013-12-01 19:20:17Z

1

The Hive equivalent of insert .. select is one of:

1. from (table 2 query)
insert [overwrite] table <table1> [partition clause if partitioned table]

OR

2. create table table1 as select <table2 query>

Note the #2 (CTAS) is not reliable. You would be better served by doing #1 in conjunction with either an explicit table creation or something like:

create table <table1> **like** table2

answered Dec 1, 2013 at 19:20

WestCoastProjects

63.9k109 gold badges368 silver badges636 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I load Hive table with complex data types using INSERT-SELECT query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related