How to create table in Hive with specific column values from another table

user_12610108 0
Viewed 645

I am new to Hive and have some problems. I try to find a answer here and other sites but with no luck... I also tried many different querys that come to my mind, also without success.

I have my source table and i want to create new table like this.

Were:

  • id would be number of distinct counties as auto increment numbers and primary key
  • counties as distinct names of counties (from source table)
hive hadoop hql
3 Answers

You could follow this approach.

A CTAS(Create Table As Select) with your example this CTAS could work

CREATE TABLE t_county 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE AS
WITH t AS(
SELECT DISTINCT county, ROW_NUMBER() OVER() AS id
FROM counties)
SELECT id, county
FROM t;

You cannot have primary key or foreign keys on Hive as you have primary key on RBDMSs like Oracle or MySql because Hive is schema on read instead of schema on write like Oracle so you cannot implement constraints of any kind on Hive.

user_85714980

I can not give you the exact answer because of it suppose to you must try to do it by yourself and then if you have a problem or a doubt come here and tell us. But, what i can tell you is that you can use the insertstatement to create a new table using data from another table, I.E:

create table CARS (name string);
insert table CARS select x, y from TABLE_2;

You can also use the overwrite statement if you desire to delete all the existing data that you have inside that table (CARS).

So, the operation will be

CREATE TABLE ==> INSERT OPERATION (OVERWRITE?) + QUERY OPERATION
user_62461070

Hive is not an RDBMS database, so there is no concept of primary key or foreign key. But you can add auto increment column in Hive. Please try as:

Create table new_table as 
select reflect("java.util.UUID", "randomUUID") id, countries from my_source_table;
user_124016980
Related