Create Table With Delimiter and SELECT – Apache Hive

The following example shows how to create a table in Apache Hive with different delimiters, and with and without default location, a text file format as underlying structure, and a SELECT command.

Tabular Delimiter:
CREATE TABLE TestTable
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE
LOCATION ‘/user/cloudera/exttest’
AS
SELECT col1, col2 FROM SourceTable1 FROM WHERE <conditions>…

Comma Delimiter with default (/user/hive/datawarehouse) location:
CREATE TABLE TestTable
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
STORED AS TEXTFILE
AS
SELECT col1, col2 FROM SourceTable1 FROM WHERE <conditions>…