CREATE HIVEFORMAT TABLE

Description

The CREATE TABLE statement defines a new table using Hive format.

Syntax

  1. CREATE [ EXTERNAL ] TABLE [ IF NOT EXISTS ] table_identifier
  2.     [ ( col_name1[:] col_type1 [ COMMENT col_comment1 ], ... ) ]
  3.     [ COMMENT table_comment ]
  4.     [ PARTITIONED BY ( col_name2[:] col_type2 [ COMMENT col_comment2 ], ... ) 
  5.         | ( col_name1, col_name2, ... ) ]
  6.     [ CLUSTERED BY ( col_name1, col_name2, ...) 
  7.         [ SORTED BY ( col_name1 [ ASC | DESC ], col_name2 [ ASC | DESC ], ... ) ] 
  8.         INTO num_buckets BUCKETS ]
  9.     [ ROW FORMAT row_format ]
  10.     [ STORED AS file_format ]
  11.     [ LOCATION path ]
  12.     [ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
  13.     [ AS select_statement ]

Note that, the clauses between the columns definition clause and the AS SELECT clause can come in as any order. For example, you can write COMMENT table_comment after TBLPROPERTIES.

Parameters

  • table_identifier

    Specifies a table name, which may be optionally qualified with a database name.

    Syntax: [ database_name. ] table_name

  • EXTERNAL

    Table is defined using the path provided as LOCATION, does not use default location for this table.

  • PARTITIONED BY

    Partitions are created on the table, based on the columns specified.

  • CLUSTERED BY

    Partitions created on the table will be bucketed into fixed buckets based on the column specified for bucketing.

    NOTE: Bucketing is an optimization technique that uses buckets (and bucketing columns) to determine data partitioning and avoid data shuffle.

  • SORTED BY

    Specifies an ordering of bucket columns. Optionally, one can use ASC for an ascending order or DESC for a descending order after any column names in the SORTED BY clause. If not specified, ASC is assumed by default.

  • INTO num_buckets BUCKETS

    Specifies buckets numbers, which is used in CLUSTERED BY clause.

  • row_format

    Specifies the row format for input and output. See HIVE FORMAT for more syntax details.

  • STORED AS

    File format for table storage, could be TEXTFILE, ORC, PARQUET, etc.

  • LOCATION

    Path to the directory where table data is stored, which could be a path on distributed storage like HDFS, etc.

  • COMMENT

    A string literal to describe the table.

  • TBLPROPERTIES

    A list of key-value pairs that is used to tag the table definition.

  • AS select_statement

    The table is populated using the data from the select statement.

Examples

  1. --Use hive format
  2. CREATE TABLE student (id INT, name STRING, age INT) STORED AS ORC;
  4. --Use data from another table
  5. CREATE TABLE student_copy STORED AS ORC
  6.     AS SELECT * FROM student;
  8. --Specify table comment and properties
  9. CREATE TABLE student (id INT, name STRING, age INT)
  10.     COMMENT 'this is a comment'
  11.     STORED AS ORC
  12.     TBLPROPERTIES ('foo'='bar');  
  14. --Specify table comment and properties with different clauses order
  15. CREATE TABLE student (id INT, name STRING, age INT)
  16.     STORED AS ORC
  17.     TBLPROPERTIES ('foo'='bar')
  18.     COMMENT 'this is a comment';
  20. --Create partitioned table
  21. CREATE TABLE student (id INT, name STRING)
  22.     PARTITIONED BY (age INT)
  23.     STORED AS ORC;
  25. --Create partitioned table with different clauses order
  26. CREATE TABLE student (id INT, name STRING)
  27.     STORED AS ORC
  28.     PARTITIONED BY (age INT);
  30. --Use Row Format and file format
  31. CREATE TABLE student (id INT, name STRING)
  32.     ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
  33.     STORED AS TEXTFILE;
  35. --Use complex datatype
  36. CREATE EXTERNAL TABLE family(
  37.         name STRING,
  38.         friends ARRAY<STRING>,
  39.         children MAP<STRING, INT>,
  40.         address STRUCT<street: STRING, city: STRING>
  41.     )
  42.     ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ESCAPED BY '\\'
  43.     COLLECTION ITEMS TERMINATED BY '_'
  44.     MAP KEYS TERMINATED BY ':'
  45.     LINES TERMINATED BY '\n'
  46.     NULL DEFINED AS 'foonull'
  47.     STORED AS TEXTFILE
  48.     LOCATION '/tmp/family/';
  50. --Use predefined custom SerDe
  51. CREATE TABLE avroExample
  52.     ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
  53.     STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
  54.         OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
  55.     TBLPROPERTIES ('avro.schema.literal'='{ "namespace": "org.apache.hive",
  56.         "name": "first_schema",
  57.         "type": "record",
  58.         "fields": [
  59.                 { "name":"string1", "type":"string" },
  60.                 { "name":"string2", "type":"string" }
  61.             ] }');
  63. --Use personalized custom SerDe(we may need to `ADD JAR xxx.jar` first to ensure we can find the serde_class,
  64. --or you may run into `CLASSNOTFOUND` exception)
  65. ADD JAR /tmp/hive_serde_example.jar;
  67. CREATE EXTERNAL TABLE family (id INT, name STRING)
  68.     ROW FORMAT SERDE 'com.ly.spark.serde.SerDeExample'
  69.     STORED AS INPUTFORMAT 'com.ly.spark.example.serde.io.SerDeExampleInputFormat'
  70.         OUTPUTFORMAT 'com.ly.spark.example.serde.io.SerDeExampleOutputFormat'
  71.     LOCATION '/tmp/family/';
  73. --Use `CLUSTERED BY` clause to create bucket table without `SORTED BY`
  74. CREATE TABLE clustered_by_test1 (ID INT, AGE STRING)
  75.     CLUSTERED BY (ID)
  76.     INTO 4 BUCKETS
  77.     STORED AS ORC
  79. --Use `CLUSTERED BY` clause to create bucket table with `SORTED BY`
  80. CREATE TABLE clustered_by_test2 (ID INT, NAME STRING)
  81.     PARTITIONED BY (YEAR STRING)
  82.     CLUSTERED BY (ID, NAME)
  83.     SORTED BY (ID ASC)
  84.     INTO 3 BUCKETS
  85.     STORED AS PARQUET