Syntax
create table {new_table} like {existing_table}
Step 1: Create table emp which stores employees information.
CREATE TABLE emp (
id INT,
name STRING,
hobbies ARRAY<STRING>,
technology_experience MAP<STRING,STRING>,
gender_age STRUCT<gender:STRING,age:INT>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE;
hive> CREATE TABLE emp (
> id INT,
> name STRING,
> hobbies ARRAY<STRING>,
> technology_experience MAP<STRING,STRING>,
> gender_age STRUCT<gender:STRING,age:INT>
> )
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '|'
> COLLECTION ITEMS TERMINATED BY ','
> MAP KEYS TERMINATED BY ':'
> STORED AS TEXTFILE;
OK
Time taken: 0.08 seconds
Let’s add some data to emp table.
Step 2: Create empInfo.txt file with below content.
empInfo.txt
1|Hari|Football,Cricket|Java:3.4Yrs,C:4.5Yrs|Male,30 2|Chamu|Trekking,Watching movies|Selenium:5.6Yrs|Feale,38 3|Sailu|Chess,Listening to music|EmbeddedC:9Yrs|Femle,32 4|Gopi|Cricket|Datastage:11Yrs|Male,32
Step 3: load empInfo.txt file content to emp table.
hive> LOAD DATA LOCAL INPATH '/Users/krishna/Documents/empInfo.txt' into table emp;;
Loading data to table default.emp
OK
Time taken: 0.593 seconds
Step 4: Print the content of emp table using select statement.
hive> select * from emp;
OK
1 Hari ["Football","Cricket"] {"Java":"3.4Yrs","C":"4.5Yrs"} {"gender":"Male","age":30}
2 Chamu ["Trekking","Watching movies"] {"Selenium":"5.6Yrs"} {"gender":"Feale","age":38}
3 Sailu ["Chess","Listening to music"] {"EmbeddedC":"9Yrs"} {"gender":"Femle","age":32}
4 Gopi ["Cricket"] {"Datastage":"11Yrs"} {"gender":"Male","age":32}
Time taken: 0.1 seconds, Fetched: 12 row(s)
Step 5: Execute below command to create emp1 from emp.
create table emp1 like emp
hive> create table emp1 like emp;
OK
Time taken: 0.06 seconds
Above statement copies only the data definition but it not copies the content. You can confirm the same by querying.
hive> select * from emp1;
OK
Time taken: 0.083 seconds
No comments:
Post a Comment