CODEWITHSUNDEEP
hiveexternalwhere-clause
anthonya
anthonya

Reputation: 565

CREATE EXTERNAL TABLE with clause in Hive

I would like to know if it is possible to create an external table in Hive according to a condition (I mean a WHERE) ?

Upvotes: 1

Views: 8511

Answers (4)

Amardeep Flora
Amardeep Flora

Reputation: 1380

CREATE TABLE myTable AS
SELECT a,b,c FROM selectTable;

Upvotes: 1

Roy Miller
Roy Miller

Reputation: 119

You cannot create an external table with Create Table As Select (CTAS) in Hive. But you can create the external table first and insert data into the table from any other table with your filter criteria. Below is an example of creating a partitioned external table stored as ORC and inserting records into that table.

CREATE EXTERNAL TABLE `table_name`( 
  `column_1` bigint, 
  `column_2` string)
PARTITIONED BY (
  `partition_column_1` string,
  `partition_column_2` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  '${dataWarehouseDir}/table_name'
TBLPROPERTIES (
  'orc.compress'='ZLIB');

set hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE table_name PARTITION(partition_column_1, partition_column_2)
SELECT column_1, column_2, partition_column_1, partition_column_2 FROM Source_Table WHERE column = "your filter criteria here";

Upvotes: 1

nobody
nobody

Reputation: 11080

You can create and external table by separating out the select statement with where clause.First create the external table and then use insert overwrite to external table using select with a where clause. 

CREATE EXTERNAL TABLE table_name
STORED AS TEXTFILE
LOCATION '/user/path/table_name';

INSERT OVERWRITE TABLE table_name
SELECT * FROM Source_Table WHERE column="something";

Upvotes: 0

Ishan Sharma
Ishan Sharma

Reputation: 63

Hive cannot create an external table with CTAS.

CTAS has these restrictions:

1.The target table cannot be a partitioned table.

2.The target table cannot be an external table.

3.The target table cannot be a list bucketing table.

Reference: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableAsSelect(CTAS)

Alternately, You can create an external table and insert into the table with select query.

Upvotes: 0

Related Questions