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

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 ASWITH t AS(SELECT DISTINCT county, ROW_NUMBER() OVER() AS idFROM counties)SELECT id, countyFROM 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.

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

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;