CREATE Statements

With Hive dialect, the following CREATE statements are supported for now:

  • CREATE DATABASE
  • CREATE TABLE
  • CREATE VIEW
  • CREATE MARCO
  • CREATE FUNCTION

CREATE DATABASE

Description

CREATE DATABASE statement is used to create a database with the specified name.

Syntax

  1. CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  2.   [COMMENT database_comment]
  3.   [LOCATION hdfs_path]
  4.   [WITH DBPROPERTIES (property_name=property_value, ...)];

Examples

  1. CREATE DATABASE db1;
  2. CREATE DATABASE IF NOT EXISTS db1 COMMENT 'db1' LOCATION '/user/hive/warehouse/db1'
  3.     WITH DBPROPERTIES ('name'='example-db');

CREATE TABLE

Description

CREATE TABLE statement is used to define a table in an existing database.

Syntax

  1. CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  2.   [(col_name data_type [column_constraint] [COMMENT col_comment], ... [table_constraint])]
  3.   [COMMENT table_comment]
  4.   [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  5.   [
  6.     [ROW FORMAT row_format]
  7.     [STORED AS file_format]
  8.   ]
  9.   [LOCATION fs_path]
  10.   [TBLPROPERTIES (property_name=property_value, ...)]
  11.   [AS select_statment];
  13. data_type
  14.   : primitive_type
  15.   | array_type
  16.   | map_type
  17.   | struct_type
  18. primitive_type
  19.   : TINYINT
  20.   | SMALLINT
  21.   | INT
  22.   | BIGINT
  23.   | BOOLEAN
  24.   | FLOAT
  25.   | DOUBLE
  26.   | DOUBLE PRECISION
  27.   | STRING
  28.   | BINARY     
  29.   | TIMESTAMP
  30.   | DECIMAL
  31.   | DECIMAL(precision, scale)
  32.   | DATE
  33.   | VARCHAR
  34.   | CHAR 
  35. array_type
  36.   : ARRAY < data_type >
  38. array_type
  39.   : ARRAY < data_type >
  40. struct_type
  41.   : STRUCT < col_name : data_type [COMMENT col_comment], ...>
  42. row_format:
  43.   : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
  44.       [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  45.       [NULL DEFINED AS char]
  46.   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, ...)]
  47. file_format:
  48.   : SEQUENCEFILE
  49.   | TEXTFILE
  50.   | RCFILE
  51.   | ORC
  52.   | PARQUET
  53.   | AVRO
  54.   | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
  55. column_constraint:
  56.   : NOT NULL
  57. table_constraint:
  58.   : [CONSTRAINT constraint_name] PRIMARY KEY (col_name, ...)

NOTE:

  • Create temporary table is not supported yet.

Examples

  1. -- creaet non-partition table
  2. CREATE TABLE t1(key string, value string);
  4. -- creaet partitioned table
  5. CREATE TABLE pt1(key string, value string) PARTITIONED BY (year int, month int);
  7. -- creaet table with specifc format
  8. CREATE TABLE t1(key string, value string) STORED AS ORC;
  10. -- create table with specifc rowfromat
  11. CREATE TABLE t1(m MAP<BIGINT, STRING>) 
  12.   ROW FROMAT DELIMITED COLLECTION ITEMS TERMINATED BY ';'
  13.   MAP KEYS TERMINATED BY ':';
  15. -- create table as select
  16. CREATE TABLE t2 AS SELECT key, COUNT(1) FROM t1 GROUP BY key;

CREATE VIEW

Description

CREATE VIEW creates a view with the given name. If no column names are supplied, the names of the view’s columns will be derived automatically from the defining SELECT expression. (If the SELECT contains un-aliased scalar expressions such as x+y, the resulting view column names will be generated in the form _C0, _C1, etc.) When renaming columns, column comments can also optionally be supplied. (Comments are not automatically inherited from underlying columns.)

Note that a view is a purely logical object with no associated storage. When a query references a view, the view’s definition is evaluated in order to produce a set of rows for further processing by the query.

Syntax

  1. CREATE VIEW [IF NOT EXISTS] [db_name.]view_name [(column_name, ...) ]
  2.   [COMMENT view_comment]
  3.   [TBLPROPERTIES (property_name = property_value, ...)]
  4.   AS SELECT ...;

Examples

  1. CREATE VIEW IF NOT EXISTS v1
  2.     (key COMMENT 'key') 
  3.     COMMENT 'View for key=1'
  4.     AS SELECT key FROM src
  5.         WHERE key = '1';

CREATE MARCO

Description

CREATE TEMPORARY MACRO statement creates a macro using the given optional list of columns as inputs to the expression. Macros exists for the duration of the current session.

Syntax

  1. CREATE TEMPORARY MACRO macro_name([col_name col_type, ...]) expression;

Examples

  1. CREATE TEMPORARY MACRO fixed_number() 42;
  2. CREATE TEMPORARY MACRO string_len_plus_two(x string) length(x) + 2;
  3. CREATE TEMPORARY MACRO simple_add (x int, y int) x + y;

CREATE FUNCTION

Description

CREATE FUNCTION statement creates a function that is implemented by the class_name.

Syntax

Create Temporary Function

  1. CREATE TEMPORARY FUNCTION function_name AS class_name [USING JAR 'file_uri'];

The function exists for the duration of the current session.

Create Permanent Function

  1. CREATE FUNCTION [db_name.]function_name AS class_name
  2.   [USING JAR 'file_uri'];

The function is registered to metastore and will exist in all session unless the function is dropped.

Parameter

  • [USING JAR 'file_uri']

    User can use the clause to add Jar that contains the implementation of the function along with its dependencies while creating the function. The file_uri can be on local file or distributed file system. Flink will automatically download the jars for remote jars when the function is used in queries. The downloaded jars will be removed when the session exits.

Examples

  1. -- create a function assuming the class `SimpleUdf` has existed in class path
  2. CREATE FUNCTION simple_udf AS 'SimpleUdf';
  4. -- create function using jar assuming the class `SimpleUdf` hasn't existed in class path
  5. CREATE FUNCTION simple_udf AS 'SimpleUdf' USING JAR '/tmp/SimpleUdf.jar';
  7. -- create function using remote jar
  8. CREATE FUNCTION simple_udf AS 'SimpleUdf' USING JAR 'hdfs://namenode-host:port/path/SimpleUdf.jar';