All parsing of external, terminated, partitioned and stored in the table creation statement of hive

Time:2020-11-27

Starting from the statement of creating tables on the official website, my analysis is as follows:

`CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive``0.14``.``0``and later)`

`[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]`

`[COMMENT table_comment]`

`[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]`

`[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]`

`[SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive``0.10``.``0``and later)]`

`ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)`

`[STORED AS DIRECTORIES]`

`[`

`[ROW FORMAT row_format] `

`[STORED AS file_format]`

`| STORED BY``'storage.handler.class.name'``[WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive``0.6``.``0``and later)`

`]`

`[LOCATION hdfs_path]`

`[TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive``0.6``.``0``and later)`

`[AS select_statement];   -- (Note: Available in Hive``0.5``.``0``and later; not supported``for``external tables)`

`CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name`

`LIKE existing_table_or_view_name`

`[LOCATION hdfs_path];`

`data_type`

`: primitive_type`

`| array_type`

`| map_type`

`| struct_type`

`| union_type  -- (Note: Available in Hive``0.7``.``0``and later)`

`primitive_type`

`: TINYINT`

`| SMALLINT`

`| INT`

`| BIGINT`

`| BOOLEAN`

`| FLOAT`

`| DOUBLE`

`| DOUBLE PRECISION -- (Note: Available in Hive``2.2``.``0``and later)`

`| STRING`

`| BINARY      -- (Note: Available in Hive``0.8``.``0``and later)`

`| TIMESTAMP   -- (Note: Available in Hive``0.8``.``0``and later)`

`| DECIMAL     -- (Note: Available in Hive``0.11``.``0``and later)`

`| DECIMAL(precision, scale)  -- (Note: Available in Hive``0.13``.``0``and later)`

`| DATE        -- (Note: Available in Hive``0.12``.``0``and later)`

`| VARCHAR     -- (Note: Available in Hive``0.12``.``0``and later)`

`| CHAR        -- (Note: Available in Hive``0.13``.``0``and later)`

`array_type`

`: ARRAY < data_type >`

`map_type`

`: MAP < primitive_type, data_type >`

`struct_type`

`: STRUCT < col_name : data_type [COMMENT col_comment], ...>`

`union_type`

`: UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive``0.7``.``0``and later)`

`row_format`

`: DELIMITED [FIELDS TERMINATED BY``char``[ESCAPED BY``char``]] [COLLECTION ITEMS TERMINATED BY``char``]`

`[MAP KEYS TERMINATED BY``char``] [LINES TERMINATED BY``char``]`

`[NULL DEFINED AS``char``]   -- (Note: Available in Hive``0.13``and later)`

`| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]`

`file_format:`

`: SEQUENCEFILE`

`| TEXTFILE    -- (Default, depending on hive.``default``.fileformat configuration)`

`| RCFILE      -- (Note: Available in Hive``0.6``.``0``and later)`

`| ORC         -- (Note: Available in Hive``0.11``.``0``and later)`

`| PARQUET     -- (Note: Available in Hive``0.13``.``0``and later)`

`| AVRO        -- (Note: Available in Hive``0.14``.``0``and later)`

`| JSONFILE    -- (Note: Available in Hive``4.0``.``0``and later)`

`| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname`

`column_constraint_specification:`

`: [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]`

`default_value:`

`: [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] `

`constraint_specification:`

`: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]`

`[, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]`

`[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE `

`[, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]`

`[, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]`

External table or management table — external

Separator setting — terminated

Row format delimited separator sets the start statement
Fields terminated by: sets the separator between fields
Collection items terminated by: set the separator between items of a complex type (array, struct) field
Map keys terminated by: set the separator between key values of a complex type (map) field
Lines terminated by: sets the line to line separator
For example:

Recommended Today

PHP 12th week function learning record

sha1() effect sha1()Function to evaluate the value of a stringSHA-1Hash. usage sha1(string,raw) case <?php $str = “Hello”; echo sha1($str); ?> result f7ff9e8b7bb2e09b70935a5d785e0cc5d9d0abf0 sha1_file() effect sha1_file()Function calculation fileSHA-1Hash. usage sha1_file(file,raw) case <?php $filename = “test.txt”; $sha1file = sha1_file($filename); echo $sha1file; ?> result aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d similar_text() effect similar_text()Function to calculate the similarity between two strings. usage similar_text(string1,string2,percent) case […]