HiveSQL in HDInsight: Create Table

HiveSQL is SQL-like queries executed against projected tabular schemas over HDFS folders.

To create an Hive table, issue the statement below:

CREATE TABLE table1 (col1 STRING, ool2, INT)
ROW DELIMITED FIELDS TERMINATED BY ' ';

The statement above will create a table at its default location as /hive/warehouse/table1, and as an internal table.

The statement below will create a table to its custom location at /data/table2:

CREATE TABLE table2 (col1 STRING, ool2, INT) 
ROW DELIMITED FIELDS TERMINATED BY ' '
STORED AS TEXTFILE LOCATION '/data/table2';

Internal tables mean that when the tables dropped, the folders are deleted along with.

To retain the data in folders, create external tables in a statement like this below:

CREATE EXTERNAL TABLE table2 (col1 STRING, ool2, INT)
ROW DELIMITED FIELDS TERMINATED BY ' ' 
STORED AS TEXTFILE LOCATION '/data/table2';

Hive data types used are simple, here are those:

Numeric:

  • Integers: TINYINT, SMALLINT, INT, BIGINT
  • Fractional: FLOAT, DOUBLE, DECIMAL

Character

  • STRING, VARCHAR, CHAR

Date/Time

  • TIMESTAMP
  • DATE

Special:

  • Boolean, Binary, Array, MAP, Struct, Uniontype
my signature
February 24, 2018