1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
CREATE TABLE tablename
(
id_column INT,
othercolumn STRING,
generated_always_as_expression DATE GENERATED ALWAYS AS (CAST(birth_date AS DATE)),
generated_by_default BIGINT GENERATED BY DEFAULT AS IDENTITY,
generated_always BIGINT GENERATED ALWAYS AS IDENTITY,
generated_column_start_with BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 10),
generated_column_increment_by BIGINT GENERATED ALWAYS AS IDENTITY (INCREMENT BY 5),
generated_column_start_with_increment_by BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 10 INCREMENT BY 5)
)
USING DELTA
LOCATION "s3://someplace"
CLUSTER BY (id_column);
OPTIMIZE tablename;
OPTIMIZE tablename
WHERE date >= current_timestamp() - INTERVAL 1 day
ZORDER BY (eventType, eventTime);
-- Creates a Delta table
CREATE TABLE student (id INT, name STRING, age INT);
-- Use data from another table
CREATE TABLE student_copy AS SELECT * FROM student;
-- Creates a CSV table from an external directory
CREATE TABLE student USING CSV LOCATION '/path/to/csv_files';
-- Specify table comment and properties
CREATE TABLE student (id INT, name STRING, age INT)
COMMENT 'this is a comment'
TBLPROPERTIES ('foo'='bar');
-- Specify table comment and properties with different clauses order
CREATE TABLE student (id INT, name STRING, age INT)
TBLPROPERTIES ('foo'='bar')
COMMENT 'this is a comment';
-- Create partitioned table
CREATE TABLE student (id INT, name STRING, age INT)
PARTITIONED BY (age);
-- Create a table with a generated column
CREATE TABLE rectangles(a INT, b INT,
area INT GENERATED ALWAYS AS (a * b));
-- Create a table with a primary key
CREATE TABLE rectangles(a INT, b INT PRIMARY KEY);
-- Create a table with a not null primary key
CREATE TABLE rectangles(a INT NOT NULL, b INT NOT NULL PRIMARY KEY);
-- Create a table with a foreign key relation
CREATE OR REPLACE TABLE TABLE1 (
DATE_VALUE DATE NOT NULL
CONSTRAINT DATE_CONSTRAINT
FOREIGN KEY REFERENCES TABLE2
);
-- Create a table with a column with default value
CREATE TABLE student (id INT, name STRING DEFAULT 'bobby tables', age INT);
-- Create a table with non nullable column with default value
CREATE TABLE student (id INT, name STRING NOT NULL DEFAULT 'bobby tables', age INT);
-- Create a table with a default timestamp
CREATE TABLE clock (
which_time TIMESTAMP DEFAULT current_timestamp()
);
-- Create a table with mixing default value and constraints
CREATE TABLE clock (
which_time TIMESTAMP CONSTRAINT clock_pk PRIMARY KEY DEFAULT current_timestamp() NOT NULL
);
-- Creates a table using identifier
CREATE TABLE IDENTIFIER('student') (id INT, name STRING, age INT);
CREATE TABLE data_engineering.test_sqlfluff_generated
(
test_generated_always_as_id BIGINT GENERATED ALWAYS AS IDENTITY
, test_generated_default_as_id BIGINT GENERATED BY DEFAULT AS IDENTITY
, test_generated_always_as_id_not_null BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL
, test_generated_default_as_id_not_null BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL
, test_not_null_generated_always_as_id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY
, test_not_null_generated_default_as_id BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY
)
USING DELTA;
CREATE TABLE cluster_by_table_auto (
col1 STRING
)
USING DELTA
CLUSTER BY AUTO;
CREATE TABLE cluster_by_table_none (
col1 STRING
)
USING DELTA
CLUSTER BY NONE;
|
