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];
  12. data_type
  13. : primitive_type
  14. | array_type
  15. | map_type
  16. | struct_type
  17. primitive_type
  18. : TINYINT
  19. | SMALLINT
  20. | INT
  21. | BIGINT
  22. | BOOLEAN
  23. | FLOAT
  24. | DOUBLE
  25. | DOUBLE PRECISION
  26. | STRING
  27. | BINARY
  28. | TIMESTAMP
  29. | DECIMAL
  30. | DECIMAL(precision, scale)
  31. | DATE
  32. | VARCHAR
  33. | CHAR
  34. array_type
  35. : ARRAY < data_type >
  36. array_type
  37. : ARRAY < data_type >
  38. struct_type
  39. : STRUCT < col_name : data_type [COMMENT col_comment], ...>
  40. row_format:
  41. : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
  42. [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  43. [NULL DEFINED AS char]
  44. | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, ...)]
  45. file_format:
  46. : SEQUENCEFILE
  47. | TEXTFILE
  48. | RCFILE
  49. | ORC
  50. | PARQUET
  51. | AVRO
  52. | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
  53. column_constraint:
  54. : NOT NULL
  55. table_constraint:
  56. : [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);
  3. -- creaet partitioned table
  4. CREATE TABLE pt1(key string, value string) PARTITIONED BY (year int, month int);
  5. -- creaet table with specifc format
  6. CREATE TABLE t1(key string, value string) STORED AS ORC;
  7. -- create table with specifc rowfromat
  8. CREATE TABLE t1(m MAP<BIGINT, STRING>)
  9. ROW FROMAT DELIMITED COLLECTION ITEMS TERMINATED BY ';'
  10. MAP KEYS TERMINATED BY ':';
  11. -- create table as select
  12. 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';
  3. -- create function using jar assuming the class `SimpleUdf` hasn't existed in class path
  4. CREATE FUNCTION simple_udf AS 'SimpleUdf' USING JAR '/tmp/SimpleUdf.jar';
  5. -- create function using remote jar
  6. CREATE FUNCTION simple_udf AS 'SimpleUdf' USING JAR 'hdfs://namenode-host:port/path/SimpleUdf.jar';