SQL语句

更新时间：2019-07-20

Alter Database

ALTER [DATABASE|SCHEMA] db_name SET DBPROPERTIES (key1=val1, ...)

说明

该语句用于设置指定数据库的属性，如果某个属性已经在数据库中进行过设置，该语句将以新的配置覆盖旧的配置。

语法

[DATABASE|SCHEMA]
SCHEMA和DATABASE的使用是可互换的，它们的意思是一样的。

db_name
要修改的数据库，不写时使用当前的数据库。

DBPROPERTIES (key1=val1, ...)
为数据库的dbproperties设置键值对属性值，来描述数据库属性信息。key为数据库的属性名称,可以修改创建数据库的时候指定的属性，也可以设置新的属性。

举例

例如有数据库testdb创建的时候未设置creator，日期date为2019-01-01。用alter语句设置creator为zhangsan，修改日期date为2019-06-15。

ALTER DATABASE testdb SET DBPROPERTIES('creator' = 'zhangsan', 'date' = '2019-06-15')

Alter Table or View

该语句用于对已有的table进行修改，包括RENAME、SET TBLPROPERTIES、UNSET TBLPROPERTIES、Set SerDe or SerDe properties。

Rename table or view

ALTER [TABLE|VIEW] [db_name.]table_name RENAME TO [db_name.]new_table_name

说明

该语句用于重命名已存在的表或者视图，如果目标表名已经存在了，将会提示错误信息。

举例

将default数据库下的table重命名为tabletest，如果未指定db，则在当前db下进行。

ALTER TABLE default.table RENAME TO default.tabletest

Set table or view properties

ALTER [TABLE|VIEW] table_name SET TBLPROPERTIES (key1=val1, key2=val2, ...)

说明

该语句用于设置指定表或者视图的属性，如果某个属性已经在表或者视图中进行过设置，该语句将以新的配置覆盖旧的配置。

举例

用alter...set语句为tabletest设置属性date, 值为new_table_20190615。

ALTER TABLE tabletest SET TBLPROPERTIES ('date'='new_table_20190615')

Drop table or view properties

ALTER (TABLE|VIEW) table_name UNSET TBLPROPERTIES
    [IF EXISTS] (key1, key2, ...)

说明

该语句用于删除表或者视图的配置属性，如果被指定的属性不存在，将会抛出异常。

语法

[IF EXISTS]
当指定的属性存在时，尝试删除；不存在时不报错。

table_name
要删除的表或者视图的名称。

举例

用alter...unset语句将tabletest的date删除。

ALTER TABLE tabletest2 UNSET TBLPROPERTIES('date')

Set SerDe or SerDe properties

ALTER TABLE table_name [PARTITION part_spec] SET SERDE serde
    [WITH SERDEPROPERTIES (key1=val1, key2=val2, ...)]

ALTER TABLE table_name [PARTITION part_spec]
    SET SERDEPROPERTIES (key1=val1, key2=val2, ...)

part_spec:
    : (part_col_name1=val1, part_col_name2=val2, ...)

说明

该语句用于设置表或者分区的SerDe或SerDe属性，如果被指定的SerDe属性已经存在，新的配置将会覆盖旧的配置。

注意

设置SerDe仅适用于通过Hive语法创建的表。

Alter Table Partitions

该语句用于对Table的分区partition进行修改，包括ADD PARTITION、RENAME、DROP PARTITION、SET LOCATION path几种操作。

ADD Partition

ALTER TABLE table_name ADD [IF NOT EXISTS]
    (PARTITION part_spec [LOCATION path], ...)

part_spec:
    : (part_col_name1=val1, part_col_name2=val2, ...)

说明

该语句用于向表中添加分区，若被添加分区的表为映射表，则可选择性的为每个添加的分区添加自定义位置。

语法

[IF NOT EXISTS]
当指定的分区不存在时添加该分区，分区已存在时不报错。

[LOCATION path]
指定分区存储路径。如不指定，会使用默认的存储路径。

举例

创建一个20190609分区，指定LOCATION为一个文件系统的路径。

ALTER TABLE tabletest ADD IF NOT EXISTS
    PARTITION (dt = '20190609')
    LOCATION '/user/zhangsan/dt=20130609'

创建多个分区，并分别指定各自的存储路径。

ALTER TABLE tabletest3 ADD 
    PARTITION (dt='20190610', city='beijing')
    LOCATION '/user/zhangsan/bj/dt=20130610' 
    PARTITION (dt='20190611', city='beijing')
    LOCATION '/user/zhangsan/bj/dt=20130611'

RENAME Partition

ALTER TABLE table_name PARTITION part_spec RENAME TO PARTITION part_spec

part_spec:
    : (part_col_name1=val1, part_col_name2=val2, ...)

说明

该语句用于重命名分区，注意不是重命名分区的字段名，只能重命名分区的名称。

注意

该操作仅适用于通过Hive语法创建的表。

举例

将分区dt='20190613',city='beijing'这个分区修改为dt='20190613',city='shanghai'。

ALTER TABLE tabletest3
    PARTITION (dt='20190613', city='beijing')
    RENAME TO PARTITION (dt='20190613', city='shanghai')

DROP Partition

ALTER TABLE table_name DROP [IF EXISTS] (PARTITION part_spec, ...)

part_spec:
    : (part_col_name1=val1, part_col_name2=val2, ...)

说明

该语句用于删除一个表或者视图的分区。

语法

[IF EXISTS]
当指定的分区存在时删除该分区，分区不存在时不报错。

注意

该操作仅适用于通过Hive语法创建的表。

举例

将dt='20190613',city='shanghai'这个分区删除。

ALTER TABLE tabletest3 DROP IF EXISTS 
    PARTITION (dt='20190613', city='shanghai')

SET LOCATION path

ALTER TABLE table_name PARTITION part_spec SET LOCATION path

part_spec:
    : (part_col_name1=val1, part_col_name2=val2, ...)

说明

该语句用于为指定的分区设置指定的路径。

注意

该操作仅适用于通过Hive语法创建的表，只可对映射表执行此操作。

举例

为分区dt='20190601',city='shanghai'这个分区设置指定的路径。

ALTER TABLE tabletest3 
    PARTITION (dt='20190601', city='shanghai')
    SET LOCATION '/user/zhangsan/shanghai/dt=20190601';

Analyze Table

ANALYZE TABLE [db_name.] table_name COMPUTE STATISTICS [analyze_option]

收集表中查询优化器可以使用更优执行计划的统计信息，统计信息可以通过DESCRIBE命令查看。

Table statistics

ANALYZE TABLE [db_name.]table_name COMPUTE STATISTICS [NOSCAN]

仅收集表的基本统计信息(行数，以字节为单位的大小)。

[NOSCAN]
仅收集统计信息不需要扫描整个表(以字节为单位的大小)。

Column statistics

ANALYZE TABLE [db_name.]table_name COMPUTE STATISTICS FOR COLUMNS col1 [col2, col3 ...]

收集表和指定列的统计信息。

提示
建议尽可能使用Column statistics命令，这样会收集更多统计信息以便优化器可以找到更优的执行计划，确保语句收集所有列的统计信息。

Cache

CACHE [LAZY] TABLE tableIdentifier AS SELECT column_name[, column_name, ...] FROM [db_name.]table_name [ WHERE boolean_expression ]

缓存数据适用于简单SELECT查询使用缓存优化性能的场景，可以指定缓存的列名和查询条件，使后续查询尽可能避免扫描原始文件。此命令只适用于查询parquet表和视图，其中视图仅支持简单查询。

[LAZY]
延迟缓存表的内容，不会立即扫描整个表。

举例

CACHE TABLE my_table_cache SELECT * FROM my_table
CACHE LAZY TABLE my_table_cache1 SELECT name, age FROM my_table WHERE age>10

Cache Table

CACHE [LAZY] TABLE [db_name.]table_name

在内存中缓存表的内容，可以使后续查询尽可能的避免扫描原始文件。

Clear Cache

CLEAR CACHE

清除当前会话的所有表缓存。

Create Database

CREATE DATABASE [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION path]
    [WITH DBPROPERTIES (property_name=property_value, ...)];

说明

该命令，创建一个名称为database_name的数据库，数据库的默认存储路径为path，数据库的注解为database_comment，该数据库中存有DBPROPERTIES中填写的键值对参数。

database_comment的语法是以'.'分割的n段标识符。例如a.b.c。当仅有一段时，即a，会在元数据树的根节点创建一个名称为a的数据库。当有n段时，即a.b.c，则会在a.b这个命名空间下创建一个名称为c的数据库。

语法

[IF NOT EXISTS]
当数据库不存在时创建该数据库，数据库存在时不报错。

database_name
数据库名字，语法为 [\w_]+[.[[\w_]+]]。即以'.'分割的n段标识符。例如a.b.c。

[COMMENT database_comment]
数据库的注解，帮助使用者理解数据库的用途。

[LOCATION path]
指定数据库默认存储路径。如不指定，会使用默认的存储路径。

[WITH DBPROPERTIES (property_name=property_value, ...)]
为数据库增加键值对参数，此键值对多为查询引擎所使用，一般用户无需填写任何参数。

Create Function

CREATE [OR REPLACE] [TEMPORARY] FUNCTION [IF NOT EXISTS]
    function_name AS class_name
    [USING JAR|FILE|ARCHIVE path, ...]

说明

该命令，讲创建一个名称为function_Name的函数，该函数实际调用class_name中的相关函数。如果指定[USING JAR|FILE|ARCHIVE path, ...]则等于先执行ADD命令，之后将创建函数。
函数创建后，通过function_name即可使用函数。例如：SELECT function_name(col) FROM tbl。
函数分为临时函数和永久性函数。临时函数的作用域为当前的session。永久性函数则在所有session中均可使用。
与传统数仓不同的是，在PINGO中，通过命令，只允许创建临时函数。如需创建永久性函数，请参考用户自定义函数管理。

语法

[OR REPLACE]
使用新命令指定的函数定义替换原有的函数名为function_name的函数。

[TEMPORARY]
当指定时，创建一个临时函数，只存在于当前session中。

[IF NOT EXISTS]
当同名函数存在时，不报错。

function_name
函数名称。

class_name
函数对应的类名称。

[USING JAR\|FILE\|ARCHIVE path, …]
指定需要加载的资源。

Create Table

使用`datasource`创建`映射`表

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db_name.]table_name
    [(col_name1 col_type1 [COMMENT col_comment1], ...)]
    USING datasource
    [OPTIONS (key1=val1, key2=val2, ...)]
    [PARTITIONED BY (col_name1, col_name2, ...)]
    [CLUSTERED BY (col_name3, col_name4, ...) INTO num_buckets BUCKETS]
    [LOCATION path]
    [COMMENT table_comment]
    [TBLPROPERTIES (key1=val1, key2=val2, ...)]
    [AS select_statement]

说明

该命令，使用datasource创建一个映射表。

语法

[TEMPORARY]
session内临时表，不持久化元数据到元数据存储中。不可与[IF NOT EXISTS]同时使用。不可与[AS select_statement]同时使用。

[IF NOT EXISTS]
当同名表存在时，不报错。不可与[TEMPORARY]同时使用。

USING datasource
表使用的文件格式，可选值为TEXT,CSV, JSON, JDBC, PARQUET, ORC和LIBSVM，或者一个自定义实现 org.apache.spark.sql.sources.DataSourceRegister 的类名全称。

[OPTIONS]
表属性，用于描述或者优化表的行为。

[PARTITIONED BY]
使用指定的列对表进行分片，以优化查询。每一个分片会对应创建一个目录。

[CLUSTERED BY]
使用特定的列，将表的每一个分片数据存储到固定数量的bucket中，以减少特定查询读取的数据量。

[LOCATION]
使用指定的目录存储表的数据。指定了LOCATION的表会自动转换为映射表。

[AS select_statement]
使用 AS 指定的查询数据填入此表。不可与[TEMPORARY]同时使用，不可指定表的列信息。想创建基于此的临时表，请使用CREATE TEMPORARY VIEW。

举例

CREATE TABLE boxes (width INT, length INT, height INT) USING CSV

CREATE TEMPORARY TABLE boxes
    (width INT, length INT, height INT)
    USING PARQUET
    OPTIONS ('compression'='snappy')

CREATE TABLE rectangles
    USING PARQUET
    PARTITIONED BY (width)
    CLUSTERED BY (length) INTO 8 buckets
    AS SELECT * FROM boxes

使用类hive语法创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
    [(col_name1[:] col_type1 [COMMENT col_comment1], ...)]
    [COMMENT table_comment]
    [PARTITIONED BY (col_name2[:] col_type2 [COMMENT col_comment2], ...)]
    [ROW FORMAT row_format]
    [STORED AS file_format]
    [LOCATION path]
    [TBLPROPERTIES (key1=val1, key2=val2, ...)]
    [AS select_statement]

row_format:
    : SERDE serde_cls [WITH SERDEPROPERTIES (key1=val1, key2=val2, ...)]
    | 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]

file_format:
    : TEXTFILE | SEQUENCEFILE | RCFILE | ORC | PARQUET | AVRO
    | INPUTFORMAT input_fmt OUTPUTFORMAT output_fmt

说明

该命令，使用hive的语法创建表。

语法

[EXTERNAL]
当不使用此语法时，创建为物理表。当使用此语法时，创建的为映射表，映射表可以使用用户指定的LOCATION存储表的数据。

[IF NOT EXISTS]
当同名表存在时，不报错。不可与[TEMPORARY]同时使用。

[PARTITIONED BY]
使用指定的列对表进行分片，以优化查询。每一个分片会对应创建一个目录。

[ROW FORMAT]
使用[SERDE]语句指定自定义的序列化信息。或者，使用[DELEMITED]语句指定自定义的序列化分隔符，转义字符，空字符(null)等。

[STORED AS]
指定表数据文件的文件格式。可选的文件格式有TEXTFILE, SEQUENCEFILE, RCFILE, ORC, PARQUET 和AVRO。只有TEXTFILE,SEQUENCEFILE和RCFILE可以指定ROW FORMAT SERDE，只有TEXTFILE可以指定ROW FORMAT DELIMITED。

[LOCATION]
使用指定的目录存储表的数据。指定了LOCATION的表会自动转换为映射表。

[AS select_statement]
使用 AS 指定的查询数据填入此表。不可与[TEMPORARY]同时使用，不可指定表的列信息。想创建基于此的临时表，请使用CREATE TEMPORARY VIEW。

举例

CREATE TABLE my_table (name STRING, age INT)

CREATE TABLE my_table (name STRING, age INT)
    COMMENT 'This table is partitioned'
    PARTITIONED BY (hair_color STRING COMMENT 'This is a column comment')
    TBLPROPERTIES ('status'='staging', 'owner'='andrew')

CREATE TABLE my_table (name STRING, age INT)
    COMMENT 'This table specifies a custom SerDe'
    ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
    STORED AS
        INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
        OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'

CREATE TABLE my_table (name STRING, age INT)
    COMMENT 'This table uses the CSV format'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    STORED AS TEXTFILE

CREATE TABLE your_table
    COMMENT 'This table is created with existing data'
    AS SELECT * FROM my_table

CREATE EXTERNAL TABLE IF NOT EXISTS my_table (name STRING, age INT)
    COMMENT 'This table is created with existing data'
    LOCATION 'spark-warehouse/tables/my_existing_table'

使用另外一张表的表结构创建表

CREATE TABLE [IF NOT EXISTS] [db_name.]table_name1
    LIKE [db_name.]table_name2 [LOCATION path]

该命令，使用[db_name.]table_name2的表结构，创建一个名为[db_name.]table_name1的表。

Create Temporary View

CREATE [OR REPLACE] [GLOBAL] TEMPORARY VIEW view_name
    [(column_name [COMMENT column_comment], ...)]
    USING datasource
    [OPTIONS (property_name=property_value, ...)]

说明

该命令，将创建一个临时视图，在PINGO中一个临时视图其实是一个临时简化的SparkTable。具体信息可以参考Create Table中的SparkTable部分。

语法

[OR REPLACE]
指定后，当名称为view_name的临时视图已经存在时，将会使用新的定义替换原有的。

[GLOBAL]
指定时，创建的临时视图所有session可见，否则只有当前session可见。

[(column_name [COMMENT column_comment], …)]
指定schema，不指定时使用datasource推测的schema。

USING datasource
视图使用的文件格式，可选值为TEXT, CSV, JSON, JDBC, PARQUET, ORC和LIBSVM，或者一个自定义实现 org.apache.spark.sql.sources.DataSourceRegister 的类名全称。

[OPTIONS (property_name=property_value, …)]
datasource对应的可选项。

Create View

CREATE [OR REPLACE] [[GLOBAL] TEMPORARY] VIEW [IF NOT EXISTS] [db_name.]view_name
    [(column_name [COMMENT column_comment], ...)]
    [COMMENT view_comment]
    [TBLPROPERTIES (property_name = property_value, ...)]
    AS SELECT ...;

说明

该命令，将在数据库db_name下创建一个名为view_name的视图。视图映射的执行语句由AS SELECT ...指定。如果SELECT语句非法，视图将会创建失败。
一旦视图创建完毕，其schema将会冻结，其依赖的表的schema变化将无法反馈到视图上。一旦依赖的表的schema变化，导致视图和表的schema不兼容，对视图的查询将会失败。
视图是只读的，不可以写入数据。

语法

[OR REPLACE]
使用新的视图替换已存在的同名视图，不可与[IF NOT EXISTS]同时使用。

[TEMPORARY]
session内临时视图，不持久化元数据到元数据存储中，不可与[db_name.]同时使用，不可与[IF NOT EXISTS]同时使用。

[GLOBAL TEMPORARY]
全局临时视图，不持久化元数据到元数据存储中，不可与[db_name.]同时使用，不可与[IF NOT EXISTS]同时使用。

[IF NOT EXISTS]
当同名视图存在时，不报错。不可与[OR REPLACE]同时使用，不可与[[GLOBAL] TEMPORARY]同时使用。

[db_name.]
指定创建视图的数据库，不指定时使用当前数据库。不可与[TEMPORARY]同时使用，不可与[GLOBAL TEMPORARY]同时使用。

[(column_name [COMMENT column_comment], ...)]
指定视图的列名和描述。指定时，列数量必须与子查询语句列数量相同。不指定时，视图使用子查询语句中表的列名做为自己的列名。

[COMMENT view_comment]
视图的描述信息。

[TBLPROPERTIES (property_name = property_value, …)]
视图参数，由查询引擎使用，一般用户无需填写。

AS SELECT ...
子查询语句，即视图所映射的查询语句，必须为当前合法的查询语句。

Describe Database

DESCRIBE DATABASE [EXTENDED] db_name

查看指定数据库的元数据(名称，注释和存储路径)。当数据库不存在时抛出异常。

[EXTENDED]
查看数据库键值对属性。

举例

DESCRIBE DATABASE EXTENDED my_db

Describe Function

DESCRIBE FUNCTION [EXTENDED] function_name

查看现有函数的元数据(实现类和用法)。当该函数不存在时抛出异常。

[EXTENDED]
查看扩展的使用信息。

举例

DESCRIBE FUNCTION EXTENDED AVG

Describe Table

DESCRIBE [EXTENDED] [db_name.]table_name

查看指定表的元数据(列名，数据类型和注释)。当该表不存在时抛出异常。

[EXTENDED]
查看表的详细信息，包括所属的数据库，表类型，存储信息和键值对属性。

举例

DESCRIBE EXTENDED my_db.my_table

Describe Partition

DESCRIBE [EXTENDED] [db_name.]table_name PARTITION partition_spec

查看指定分区的元数据，partition_spec为需要提供所有分区列的值。

[EXTENDED]
查看表和分区存储的基本信息。

举例

DESCRIBE EXTENDED my_db.my_table PARTITION (dt='20190620')

Describe Columns

DESCRIBE [EXTENDED] [db_name.]table_name column_name

查看表中指定列的元数据。

[EXTENDED]
查看有关指定列的详细信息，包括命令ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column_name [column_name，...]收集的列统计信息。

Describe Formatted

DESCRIBE FORMATTED [db_name.]table_name

用表格形式显示表的元数据。

Drop Database

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

说明

该命令，将删除名为database_name的数据库或者命名空间。

语法

[IF EXISTS]
当数据库存在时，尝试删除。当数据库不存在时不报错。

database_name
要删除的数据库名称。

[RESTRICT|CASCADE]
RESTRICT是默认行为，CASCADE表示同时删除数据库下的表。

Drop Function

DROP [TEMPORARY] FUNCTION [IF EXISTS] function_name

说明

该命令，删除一个函数名为function_name的函数。
与传统数仓不同的是，在PINGO中，通过命令，只允许删除临时函数。如需删除永久性函数，请参考用户自定义函数管理。

语法

[TEMPORARY]
当指定时，删除一个临时函数。

[IF EXISTS]
当删除的函数不存在时，不报错。

function_name
函数名称。

Drop Table

DROP TABLE [IF EXISTS] [db_name.]table_name [PURGE];

说明

该命令，将删除指定名称的表。
非回收站中的表，被删除时将会被移动到回收站中，并且会被重新命名，同时会收回除操作人外所有用户对此表的权限。若想恢复被删除到回收站的表，使用alter table rename语法。
回收站中的表。如果表是物理表，此命令会同时删除数据和元数据。如果底层存储配置了trash，并且没有使用PURGE，则数据会被移动到.Trash/Current directory目录。以方便误删表时手动恢复数据。
如果表是映射表的，则只删除元数据。
删除一个被视图引用的表不会得到任何的错误提示，此时对应的视图无法使用，需要手动重建对应的表或者删除该视图。

语法

[IF EXISTS]
当表存在时，尝试删除。当表不存在时不报错。

[db_name.]
删除的表所属的数据库，不写时使用当前的数据库。如果视图不在回收站中，则移动至回收站。同时会收回除操作人外所有用户对此视图的权限。如果视图已经在回收站中，则被彻底删除。若想恢复被删除到回收站的表，使用alter table rename语法。

table_name
要删除的表名称。

[PURGE]
直接删除数据，而不移动到.Trash/Current directory。

Drop View

DROP VIEW [IF EXISTS] [db_name.]view_name

该命令，删除db_name下名称为view_name的视图

[IF EXISTS]
当视图存在时，尝试删除。当视图不存在时不报错。

[db_name.]
删除的视图所属的数据库，不写时使用当前的数据库。

table_name
要删除的视图名称。

Explain

EXPLAIN [EXTENDED | CODEGEN] statement

提供有关语句的详细执行计划信息，而无需实际运行。默认情况下仅输出有关物理执行计划的信息，不支持 DESCRIBE TABLE语句。

[EXTENDED]
在分析和优化的前后，分别输出逻辑执行计划的相关信息。

[CODEGEN]
如果存在则输出生成的语句代码。

Insert

从查询语句插入

INSERT INTO [TABLE] [db_name.]table_name [PARTITION part_spec] select_statement

INSERT OVERWRITE TABLE [db_name.]table_name [PARTITION part_spec] select_statement

part_spec:
  : (part_col_name1=val1 [, part_col_name2=val2, ...])

说明

从一个查询语句产生的结果表来插入数据到一个新的表或者分区。

语法

OVERWRITE
OVERWRITE关键字表示写时覆盖表或者分区里面已经存在的数据，否则新的数据将被添加。

举例

-- 创建一个原生Parquet分区表
CREATE TABLE data_source_tab1 (col1 INT, p1 INT, p2 INT)
  USING PARQUET PARTITIONED BY (p1, p2)

-- 添加两行到 (p1 = 3, p2 = 4)的分区
INSERT INTO data_source_tab1 PARTITION (p1 = 3, p2 = 4)
  SELECT id FROM RANGE(1, 3)

-- 使用两个新的行覆盖分区(p1 = 3, p2 = 4)
INSERT OVERWRITE TABLE default.data_source_tab1 PARTITION (p1 = 3, p2 = 4)
  SELECT id FROM RANGE(3, 5)

插入数值到表

INSERT INTO [TABLE] [db_name.]table_name [PARTITION part_spec] VALUES values_row [, values_row ...]

INSERT OVERWRITE TABLE [db_name.]table_name [PARTITION part_spec] VALUES values_row [, values_row ...]

values_row:
    : (val1 [, val2, ...])

说明

从一个列值列表中插入数据到表或者分区。

语法

OVERWRITE
OVERWRITE写覆盖表或者分区里面已经存在的数据，否则新的数据将被添加。

举例

-- 创建一个Hive serde的分区表
CREATE TABLE hive_serde_tab1 (col1 INT, p1 INT, p2 INT)
  USING HIVE OPTIONS(fileFormat 'PARQUET') PARTITIONED BY (p1, p2)

-- 添加两行到(p1 = 3, p2 = 4)分区
INSERT INTO hive_serde_tab1 PARTITION (p1 = 3, p2 = 4)
  VALUES (1), (2)

-- 使用两个新的行覆盖(p1 = 3, p2 = 4)分区
INSERT OVERWRITE TABLE hive_serde_tab1 PARTITION (p1 = 3, p2 = 4)
  VALUES (3), (4)

动态分区插入

当分区说明part_spec 没有被完全提供的时候，这时候的插入被称之为动态分区插入，也被称之为多分区插入。对于分区说明part_spec ，分区的列的值是可选的。当这个值没有提供的时候，这些列就被称为动态分区列；否则就是被成为静态分区列。比如分区指定为(p1 = 3, p2, p3) 其中有一个静态分区列 (p1)和两个动态分区列(p2和p3)。

在指定part_spec的时候，静态分区的值必须在动态分区值之前。也就是说，所有有固定值的分区列必须出现在其他没有被赋予固定值的分区列之前。

动态分区的分区值在执行的过程中确定，动态分区列最终必须在 part_spec 和输入的结果间（从行值列表或者查询语句）确定，它们是按照位置被解析的，而不是按照名字，所以顺序必须能被准确的匹配。

重要

在动态分区模式，输入的结果集将会导致大量的动态分区，这将会导致生成大量的分区目录。

OVERWRITE
OVERWRITE语义会根据目标表的类型而有些不同。

Hive SerDe 表：INSERT OVERWRITE不会删除前面的分区，而只是覆盖那些在运行阶段有数据需要的分区，这是和Apache Hive的语义匹配的。对于Hive SerDe表，Spark SQL尊重Hive相关的配置，包括hive.exec.dynamic.partition and hive.exec.dynamic.partition.mode。

Native data source 表：INSERT OVERWRITE首先删除所有配置的分区(比如，PARTITION(a=1, b)) 然后插入所有剩余的值。可以通过修改session-specific 配置spark.sql.sources.partitionOverwriteMode 为 DYNAMIC的方式，将native data source表的行为与Hive SerDe 表保持一致，缺省的模式是STATIC。

举例

-- 创建一个原生的Parquet分区表。
CREATE TABLE data_source_tab2 (col1 INT, p1 STRING, p2 STRING)
  USING PARQUET PARTITIONED BY (p1, p2)

-- 两个分区('part1', 'part1') and ('part1', 'part2')通过下面的动态分区插入被创建。
-- 动态分区列P2是通过最后的列`'part' || id`被计算出来。
INSERT INTO data_source_tab2 PARTITION (p1 = 'part1', p2)
  SELECT id, 'part' || id FROM RANGE(1, 3)

-- 一个新的分区 ('partNew1', 'partNew2') 被通过下面的插入覆盖操作添加。
INSERT OVERWRITE TABLE data_source_tab2 PARTITION (p1 = 'partNew1', p2)
  VALUES (3, 'partNew2')

-- 经过下面的插入覆盖操作,  ('part1', 'part1') 和 ('part1', 'part2') 这两个分区被丢弃。因为这两个分区都包括(p1 = 'part1', p2)。
-- 然后, 这样操作后只有两个分区 ('partNew1', 'partNew2'), ('part1', 'part1') 存在。
INSERT OVERWRITE TABLE data_source_tab2 PARTITION (p1 = 'part1', p2)
  VALUES (5, 'part1')

-- 创建一个hive serde分区表，并且用下面的三个分区填充:
-- ('part1', 'part1'), ('part1', 'part2') and ('partNew1', 'partNew2')
CREATE TABLE hive_serde_tab2 (col1 INT, p1 STRING, p2 STRING)
  USING HIVE OPTIONS(fileFormat 'PARQUET') PARTITIONED BY (p1, p2)
INSERT INTO hive_serde_tab2 PARTITION (p1 = 'part1', p2)
  SELECT id, 'part' || id FROM RANGE(1, 3)
INSERT OVERWRITE TABLE hive_serde_tab2 PARTITION (p1 = 'partNew1', p2)
  VALUES (3, 'partNew2')

-- 经过下面这个插入覆盖操作, 只有分区 ('part1', 'part1') 被新的值写覆盖。所有其他的三个分区仍然存在。
INSERT OVERWRITE TABLE hive_serde_tab2 PARTITION (p1 = 'part1', p2)
  VALUES (5, 'part1')

插入值到目录

INSERT OVERWRITE [LOCAL] DIRECTORY [directory_path]
  USING data_source [OPTIONS (key1=val1, key2=val2, ...)]
  [AS] SELECT ... FROM ...

说明

使用Spark的原生格式，插入查询语句的查询结果到directory_path 目录。如果指定的路径存在，它将被查询语句的输出结果覆盖。

语法

DIRECTORY

DIRECTORY关键字用于指定插入数据目标目录的路径。目录也可以通过在 OPTIONS 中使用path这个key值指定。如果指定的路径存在，它会被查询语句的输出结果覆盖。如果使用了LOCAL 关键字，目录是指本地的文件系统。

USING
USING关键字用于指定插入的文件格式，它可以是TEXT, CSV, JSON, JDBC, PARQUET, ORC, HIVE, DELTA, 和 LIBSVM中的一种, 或者是一个基于org.apache.spark.sql.sources.DataSourceRegister的自定义完整实现的类名。

AS
AS关键字用来指定用于填充目的目录的查询语句的输入数据。

举例

INSERT OVERWRITE DIRECTORY
USING parquet
OPTIONS ('path' '/tmp/destination/path')
SELECT key, col1, col2 FROM source_table

INSERT OVERWRITE DIRECTORY '/tmp/destination/path'
USING json
SELECT 1 as a, 'c' as b

使用Hive格式插入数据到目录

INSERT OVERWRITE [LOCAL] DIRECTORY directory_path
  [ROW FORMAT row_format] [STORED AS file_format]
  [AS] select_statement

说明

使用Hive SerDe将查询语句的结果插入到directory_path目录，如果指定的路径存在，它的数据将会被查询语句的输出结果覆盖。

注意

这个只有当Hive支持选择启用的时候才被支持。

语法

[OVERWRITE]
DIRECTORY关键字用于指定插入数据目标目录的路径。如果指定的路径存在，它会被查询语句的输出结果覆盖。如果LOCAL 关键字被使用，目录是指本地的文件系统。

[ROW FORMAT]
使用ROW FORMAT SERDE用于表示一个自定义的SerDe去插入，否则使用ROW FORMAT DELIMITED表示用一个原生的SerDe并且要指定分隔符，比如转义字符、空格字符等等。

[STORED AS]
STORED AS关键字用于指定这次插入的文件格式，它可以是TEXTFILE, SEQUENCEFILE, RCFILE, ORC, PARQUET和 AVRO中的一种。或者也可以指定你自己的输入和输出格式通过INPUTFORMAT 和 OUTPUTFORMAT。只有 TEXTFILE, SEQUENCEFILE和RCFILE 文件格式可以指定ROW FORMAT SERDE，只有TEXTFILE文件格式可以指定 ROW FORMAT DELIMITED。

[AS]
AS关键字用来指定用于填充目的目录的查询语句的输入数据。

举例

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/destination/path'
STORED AS orc
SELECT * FROM source_table where key < 10

Load Data

LOAD DATA [LOCAL] INPATH path [OVERWRITE] INTO TABLE [db_name.]table_name [PARTITION part_spec]

part_spec:
    : (part_col_name1=val1, part_col_name2=val2, ...)

说明

从一个文件加载数据到一个表或者一个表的分区。目标表不能是一个临时表。如果目标表是被分区的，那么必须提供一个分区的part_spec。

注意

这个命令只有在使用Hive 格式创建的表才被支持。

语法

[LOCAL]
LOCAL关键字指定从本地文件系统加载路径，否则使用的是缺省的文件系统。
[OVERWRITE]
OVERWRITE关键字表示删除表中存在的数据。否则，新的数据将会被添加到表中。

Refresh Table

REFRESH TABLE [db_name.]table_name

刷新与表关联的缓存，假如表先前已缓存过，下次扫描时会延迟缓存。

Reset

reset

将所有配置重置为其默认值。此后，Set命令输出将为空。慎用！

Select

基本语法

SELECT [hints, ...] [ALL|DISTINCT] named_expression[, named_expression, ...]
  FROM relation[, relation, ...]
  [lateral_view[, lateral_view, ...]]
  [WHERE boolean_expression]
  [aggregation [HAVING boolean_expression]]
  [ORDER BY sort_expressions]
  [CLUSTER BY expressions]
  [DISTRIBUTE BY expressions]
  [SORT BY sort_expressions]
  [WINDOW named_window[, WINDOW named_window, ...]]
  [LIMIT num_rows]

named_expression:
  : expression [AS alias]

relation:
  | join_relation
  | (table_name|query|relation) [sample] [AS alias]
  : VALUES (expressions)[, (expressions), ...]
        [AS (column_name[, column_name, ...])]

expressions:
  : expression[, expression, ...]

sort_expressions:
  : expression [ASC|DESC][, expression [ASC|DESC], ...]

说明

Select意指从一个或者多个"关系"中，输出数据。这段关系可以是任意种类的输入数据，比如表，视图，或者两张表的join，抑或另一段select语句的子查询。

语法

ALL

ALL 返回满足SQL语句条件的所有记录。如果没有指明这个谓词，默认为ALL。

DISTINCT

如果有多个记录的选择字段的数据相同，只返回一个。

WHERE

指定查询条件。

HAVING

指定一组行或聚合的过滤条件，经常与GROUP BY一同使用。

ORDER BY

全排序，默认顺序是升序，可以通过ASC|DESC来指定排序方式，不能与SORT BY，CLUSTER BY以及DISTRIBUTE BY一起使用。

DISTRIBUTE BY

根据一组表达式，将结果重新分区，拥有相同表达式结果的行会被哈希分配到相同的worker中。DISTRIBUTE BY不能与ORDER BY或CLUSTER BY混合使用。

SORT BY

将每个partition中的结果根据一组表达式强制排序，默认排序方式为升序。SORT BY不能与ORDER BY或CLUSTER BY混合使用。

CLUSTER BY

将结果依据一组表达式重新分区并在每个partition中强制升序排序。换句话说，CLUSTER BY是DISTRIBUTE BY + SORT BY的在表达式都是升序排序时的简化写法。所以很显然，CLUSTER BY不能与ORDER BY，DISTRIBUTE BY或者SORT BY混合使用。

WINDOW

使用窗口函数的标识符，详见Window Function。

LIMIT

限制输出行数的标识符。

VALUES

不读表，而是显式指定数值的一种表达方式。

基本语法举例

SELECT * FROM boxes;

SELECT width, length FROM boxes WHERE height=3;

SELECT DISTINCT width, length FROM boxes WHERE height=3 LIMIT 2;

SELECT * FROM VALUES (1, 2, 3) AS (width, length, height);

SELECT * FROM VALUES (1, 2, 3), (2, 3, 4) AS (width, length, height);

SELECT * FROM boxes ORDER BY width;

SELECT * FROM boxes DISTRIBUTE BY width SORT BY width;

SELECT * FROM boxes CLUSTER BY length;

SAMPLING

sample:
    | TABLESAMPLE ((integer_expression | decimal_expression) PERCENT)
    : TABLESAMPLE (integer_expression ROWS)

说明

对输入数据采样,采样方式可以是百分比,或者一个固定的数值。

采样语法举例

-- 按数值采样
SELECT * FROM boxes TABLESAMPLE (3 ROWS);

-- 按百分比采样
SELECT * FROM boxes TABLESAMPLE (25 PERCENT);

JOINS

join_relation:
    | relation join_type JOIN relation (ON boolean_expression | USING (column_name[, column_name, ...]))
    : relation NATURAL join_type JOIN relation
join_type:
    | INNER
    | (LEFT|RIGHT) SEMI
    | (LEFT|RIGHT|FULL) [OUTER]
    : [LEFT] ANTI

语法

INNER JOIN

选取两组表中选择满足join条件的所有行。

OUTER JOIN

选取两组表中所有行，将不满足join条件的行填为null。

SEMI JOIN

只选择SEMI JOIN一边表中满足join条件的行，如果另一边有多行满足条件，只取第一行，等同于IN子查询语法。

LEFT ANTI JOIN

同样只选取左边关系中的结果返回，但是仅限于不在右表中的行。

Join语法举例

SELECT * FROM boxes INNER JOIN rectangles ON boxes.width = rectangles.width;

SELECT * FROM boxes FULL OUTER JOIN rectangles USING (width, length);

SELECT * FROM boxes NATURAL JOIN rectangles;

LATERAL VIEW

lateral_view:
    : LATERAL VIEW [OUTER] function_name (expressions)
          table_name [AS (column_name[, column_name, ...])]

说明

对于每个输入的行，使用表生成函数(搭配LATERAL VIEW最常用的就是EXPLODE函数)，生成0或多行数据。

语法

LATERAL VIEW OUTER

LATERAL VIEW的衍伸用法，在生成函数没有返回行数的时候，仍然生成一个null行。

Lateral View语法举例

SELECT * FROM boxes LATERAL VIEW explode(Array(1, 2, 3)) my_view;

SELECT name, my_view.grade FROM students LATERAL VIEW OUTER explode(grades) my_view AS grade;

AGGREGATION

aggregation:
    : GROUP BY expressions [(WITH ROLLUP | WITH CUBE | GROUPING SETS (expressions))]

说明

对于一个或者多个聚合表达式进行分组。常用的聚合表达式有count, avg, min, max以及sum。

语法

GROUPING SETS

GROUPING SETS作为GROUP BY的子句，起到增强GROUP BY语义的作用，允许开发人员在GROUP BY语句后面指定多个统计选项。等价于通过UNION把为多条GROUP BY语句的查询结果聚合起来

即

-- 1.
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a,b));
-- 等价于
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b;

-- 2.
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a,b), a)
-- 等价于
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM(c) FROM tab1 GROUP BY a;

-- 3.
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS (a,b)
-- 等价于
SELECT a, null, SUM( c ) FROM tab1 GROUP BY a
UNION
SELECT null, b, SUM( c ) FROM tab1 GROUP BY b;

-- 4.
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ((a, b), a, b, ( ));
-- 等价于
SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b
UNION
SELECT a, null, SUM(c) FROM tab1 GROUP BY a
UNION
SELECT null, b, SUM(c) FROM tab1 GROUP BY b
UNION
SELECT null, null, SUM(c) FROM tab1;

CUBE

是GROUP BY的子句，设计来增强GROUP BY语义。作用是根据GROUP BY中的所有维度组合进行聚合。也就是GROUP BY a, b, c WITH CUBE等价于 GROUP BY a, b, c GROUPING SETS ((a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ())。GROUPING SET的数量是2^N，其中N是group表达式中参数的数量。

ROLLUP

同样是GROUP BY的子句，设计来增强GROUP BY语义。直观来看，ROLLUP是CUBE的一个子集，作用是返回包含GROUP BY中第一个维度的所有组合。举例来说也就是 GROUP BY a, b, c WITH ROLLUP 等价于 GROUP BY a, b, c GROUPING SETS ((a, b, c), (a, b), (a), ())。GROUPING SET的总数是N+1，其中N是group表达式中参数的数量。

聚合语法举例

SELECT height, COUNT(*) AS num_rows FROM boxes GROUP BY height;

SELECT width, AVG(length) AS average_length FROM boxes GROUP BY width;

SELECT width, length, height FROM boxes GROUP BY width, length, height WITH ROLLUP;

SELECT width, length, avg(height) FROM boxes GROUP BY width, length GROUPING SETS (width, length);

WINDOW FUNCTIONS

window_expression:
  : expression OVER window_spec

named_window:
  : window_identifier AS window_spec

window_spec:
  | window_identifier
  : ((PARTITION|DISTRIBUTE) BY expressions
        [(ORDER|SORT) BY sort_expressions] [window_frame])

window_frame:
  | (RANGE|ROWS) frame_bound
  : (RANGE|ROWS) BETWEEN frame_bound AND frame_bound

frame_bound:
  | CURRENT ROW
  | UNBOUNDED (PRECEDING|FOLLOWING)
  : expression (PRECEDING|FOLLOWING)

说明

窗口函数对于一组输入行进行计算并返回结果。一个窗口表达式通过OVER关键词标识，后接用WINDOW关键词定义的子句或者窗口说明。

语法

PARTITION BY

这里可以认为是DISTRIBUTE BY的别名，指定某些行作为分区。

ORDER BY

这里可以认为是SORT BY的别名，将同partition中的数据排序。

RANGE bound

描述窗口大小的表达式，按实际行号划分界限，意为从第几行到第几行。

ROWS bound

描述窗口大小的表达式，按相对位置划分界限，以为从当前行的前几行到当前行的后几行。

RANGE|ROWS语法举例

(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING

CURRENT ROW

使用当前行作为边界。

UNBOUNDED

使用负无穷作为下界，或者使用正无穷作为上界。

PRECEDING

当使用RANGE BOUND的时候，定义下边界的值，当使用ROW BOUND的时候，定义下边界为当前行的前几行。

FOLLOWING

当使用RANGE BOUND的时候，定义上边界的值，当使用ROW BOUND的时候，定义上边界为当前行的后几行。

窗口函数举例

-- PARTITION BY with one partitioning column, no ORDER BY or window specification
SELECT a, COUNT(b) OVER (PARTITION BY c) FROM T;

-- PARTITION BY with two partitioning columns, no ORDER BY or window specification
SELECT a, COUNT(b) OVER (PARTITION BY c, d) FROM T;

-- PARTITION BY with one partitioning column, one ORDER BY column, and no window specification
SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d) FROM T;

-- PARTITION BY with two partitioning columns, two ORDER BY columns, and no window specification
SELECT a, SUM(b) OVER (PARTITION BY c, d ORDER BY e, f) FROM T;

-- PARTITION BY with partitioning, ORDER BY, and window specification
SELECT a, SUM(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM T;
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) FROM T;
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) FROM T;
SELECT a, AVG(b) OVER (PARTITION BY c ORDER BY d ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM T;

-- 同一条query中可以同时存在几个over子句，比如下面的示例
SELECT
 a,
 COUNT(b) OVER (PARTITION BY c),
 SUM(b) OVER (PARTITION BY c)
FROM T;

-- 别名在这里同样生效, AS可有可无
SELECT
 a,
 COUNT(b) OVER (PARTITION BY c) AS b_count,
 SUM(b) OVER (PARTITION BY c) b_sum
FROM T;

-- WINDOW 子句的用法
SELECT a, SUM(b) OVER w
FROM T
WINDOW w AS (PARTITION BY c ORDER BY d ROWS UNBOUNDED PRECEDING);

HINTS

hints:
  : /*+ hint[, hint, ...] */
  hint:
    : hintName [(expression[, expression, ...])]

说明

HINTS是一种优化帮助Spark更好执行语句的语法形式。比如说，你可以指定一个map join中被广播的小表以提升join计算效率。在一个SELECT语句中，可以指定一个或者多个HINTS，这种结构被/+ ... /这样的注释结构中。一个HINTS`可以有0个或者多个参数。

目前Spark提供两种HINTS语法：

一种是上面提到的BROADCAST HINTS，用以广播小表。

BROADCAST HINTS语法举例

select /*+ MAPJOIN(time_dim) */ count(*) from
customers join orders on (o_custId = c_custId);
select /*+ BROADCAST(time_dim) */ count(*) from
customers join orders on (o_custId = c_custId);
select /*+ BROADCASTJOIN(time_dim) */ count(*) from
customers join orders on (o_custId = c_custId);

-- COALESCE and REPARTITION Hints
SELECT /*+ COALESCE(5) */ * from

另一种是COALESCE and REPARTITION HINTS，两种方式都用来调整最终的partition数量，这里解释一下二者的区别。不同于RDD操作中的两种方法，HINTS中，

COALESCE`操作只能减少分区，通过最小程度的shuffle操作合并数据。因为本身不增加stage，所以会影响原有任务的并发。
REPARTITION通过全量shuffle进行重新分区，保证数据尽可能均等的分配，所以可以增大也可以减少分区数量。因为操作本身会增加一轮stage，所以不影响原有任务的并发。

COALESCE and REPARTITION HINTS语法举例

-- COALESCE and REPARTITION Hints
SELECT /*+ COALESCE(5) */ * from customers, orders WHERE o_custId = c_custId;
SELECT /*+ REPARTITION(3) */ * from customers, orders WHERE o_custId = c_custId;

Set

SET [-v]
SET property_key[=property_value]

设置配置，返回现有配置的值，或列出所有现有的SQL配置。如果配置已经存在，则将覆盖旧值。

set -v
输出所有SQL配置的配置项和值。

set property_key
输出property_key当前的配置

举例

set spark.sql.shuffle.partitions

set property_key=property_value
设置property_key配置，如果该配置已存在，则会覆盖配置。

举例

set spark.sql.shuffle.partitions=2

Show Columns

SHOW COLUMNS (FROM | IN) [db_name.]table_name

说明

显示表名为table_name的数据表所有列名。如果表不存在，则报错。

语法

[db_name]
表所在的数据库名称

举例

-- 显示命名空间myns下的mydb数据库中名为mytable数据表的全部列名
SHOW COLUMNS IN myns.mydb.mytable

Show Create Table

SHOW CREATE TABLE [db_name.]table_name

说明

显示现有表table_name的建表语句。如果表不存在，则报错。

语法

[db_name]
表所在的数据库名称

举例

-- 显示命名空间myns下的mydb数据库中名为mytable数据表的建表语句。
SHOW CREATE TABLE myns.mydb.mytable

Show Databases

SHOW {DATABASES|SCHEMAS} [LIKE 'pattern']

说明

显示全部数据库。SHOW SCHEMAS 是 SHOW DATABASES的同义词。

语法

[LIKE]
要匹配的数据库名称。在pattern中，*匹配任意数量的字符。

举例

-- 显示名称以'def'开头的全部数据库。
SHOW DATABASES LIKE 'def*'

Show Functions

SHOW [USER|SYSTEM|ALL] FUNCTIONS ([LIKE] regex | [db_name.]function_name)

说明

显示与给定正则表达式或函数名匹配的函数。如果没有提供正则表达式或函数名称，则显示所有函数。如果声明了USER或SYSTEM，将分别显示用户定义的spark sql函数或系统定义的spark sql函数。

语法

[db_name]
函数所在的数据库名称。

[like]
此限定符只用于兼容性，对语句没有任何效果。

举例

-- 显示函数名称为'to_date'的函数
SHOW FUNCTIONS LIKE 'to_date'
SHOW FUNCTIONS 'to_date'

Show Partitions

SHOW PARTITIONS [db_name.]table_name [PARTITION part_spec]

说明

列出表的分区，按给定的分区值筛选。

语法

[db_name]
数据表所在的数据库名称。

[part_spec]
(part_col_name1=val1, part_col_name2=val2, ...)

举例

SHOW PARTITIONS mydb.mytable
SHOW PARTITIONS mydb.mytable PARTITION (date='20190618')

Show Table Properties

SHOW TBLPROPERTIES [db_name.]table_name [(property_key)]

说明

显示表指定属性集的所有属性或值。如果表不存在，则报错。TBLPROPERTIES是表的一些属性，HIVE内置了一部分属性，使用者也可以在创建表时进行自定义。

语法

[db_name]
函数所在的数据库名称。

[(property_key)]
如果包含，则只会列出名为 property_key 的属性的值。

举例

SHOW TBLPROPERTIES mydb.mytable
SHOW TBLPROPERTIES mydb.mytable ("transient_lastDdlTime")

Show Tables

SHOW TABLES [{FROM|IN} db_name] [LIKE 'pattern']

说明

显示全部数据表。

语法

[db_name]
函数所在的数据库名称。

[LIKE]
要匹配的表名称。在pattern中，*匹配任意数量的字符。

举例

-- 显示mydb数据库中名称以'def'开头的全部数据表。
SHOW TABLES FROM mydb LIKE 'def*'

Truncate Table

TRUNCATE TABLE table_name [PARTITION part_spec]

part_spec:
  : (part_col1=value1, part_col2=value2, ...)

说明

该语句用于删除表中的所有数据或者删除匹配的某一分区的数据。与DROP TABLE的区别：DROP TABLE命令不但会删除表中所有数据，还会将整个表结构从数据库中移除,如果想要重新向表中存储数据的话，必须重建该数据表,而TRUNCATE会保留表的结构，只是将数据移除。

注意

该操作的对象不能是临时表, 映射表,或者视图。

举例

对表tabletest中的分区dt='20190601'进行TRUNCATE 操作，则分区中的数据被全部删除，但是分区仍然存在。

/*执行truncate之前执行的查询*/
SELECT COUNT(1) FROM  tabletest WHERE tabletest.dt='20190601' 

/*执行结果如下*/
   count(1)
0       197

/*执行truncate操作*/
TRUNCATE TABLE tabletest partition(dt='20190601') 

/*再次执行查询语句*/
SELECT COUNT(1) FROM  tabletest WHERE tabletest.dt='20190601' 

/*执行结果如下，执行truncate操作之后，分区中的数据已经被删除*/
   count(1)
0       0

/*查看表的分区*/
show partitions tabletest

/*从查询结果看到表的分区依然存在，但是分区中的数据通过truncate操作移除了*/
     partition
0  dt=20190601
1  dt=20190602
2  dt=20190603

Uncache Table

UNCACHE TABLE [db_name.]table_name

删除与该表关联的所有缓存。

Use Database

-- 使用一个database
USE db_name;

说明

设置当前的database，声明之后的所有没有显示声明其他数据库的表，都默认在这个数据库中。如不声明，默认值为default。

Pingo扩展语法：Pingo中，特别支持了多级嵌套命名空间的database。

-- 使用拥有一个pingo_ns命名空间的database
USE pingo_ns.db_name;
-- 使用有嵌套命名空间的database
USE baidu_ns.bdg_ns.pingo_ns.db_name;

字面常量

API参考手册

百度数据工厂 Pingo

SQL语句

Alter Database

Alter Table or View

Rename table or view

Set table or view properties

Drop table or view properties

Set SerDe or SerDe properties

Alter Table Partitions

ADD Partition

RENAME Partition

DROP Partition

SET LOCATION path

Analyze Table

Table statistics

Column statistics

Cache

Cache Table

Clear Cache

Create Database

Create Function

Create Table

使用datasource创建映射表

使用类hive语法创建表

使用另外一张表的表结构创建表

Create Temporary View

Create View

Describe Database

Describe Function

Describe Table

Describe Table

Describe Partition

Describe Columns

Describe Formatted

Drop Database

Drop Function

Drop Table

Drop View

Explain

Insert

从查询语句插入

插入数值到表

动态分区插入

插入值到目录

使用Hive格式插入数据到目录

Load Data

Refresh Table

Reset

Select

基本语法

SAMPLING

JOINS

LATERAL VIEW

AGGREGATION

HINTS

Set

Show Columns

Show Create Table

Show Databases

Show Functions

Show Partitions

Show Table Properties

Show Tables

Truncate Table

Uncache Table

Use Database

使用`datasource`创建`映射`表