[TOC] #### 7.1.4.1 ALTER ROUTINE LOAD **说明** 该语法用于修改已经创建的例行导入作业,且只能修改处于 PAUSED 状态的作业。语法定义如下: `ALTER ROUTINE LOAD FOR [db.]job_name` `[job_properties]` `FROM data_source` `[data_source_properties]` * \[db.\]job\_name 指定要修改的作业名称。 * tbl\_name 指定需要导入的表的名称。 * job\_properties 指定需要修改的作业参数。目前仅支持如下参数的修改: 1. desired\_concurrent\_number 2. max\_error\_number 3. max\_batch\_interval 4. max\_batch\_rows 5. max\_batch\_size 6. jsonpaths 7. json\_root 8. strip\_outer\_array 9. strict\_mode 10. timezone * data\_source 数据源的类型。当前支持:kafka。 * data\_source\_properties 数据源的相关属性。目前仅支持: 1. kafka\_partitions 2. kafka\_offsets 3. 自定义 property,如 property.group.id > 注:kafka\_partitions 和 kafka\_offsets 用于修改待消费的 kafka partition 的offset,仅能修改当前已经消费的 partition,不能新增 partition。 **示例** (1)将 desired\_concurrent\_number 修改为 1。 `ALTER ROUTINE LOAD FOR db1.label1` `PROPERTIES` `(` `   "desired_concurrent_number" = "1"` `);` (2)将 desired\_concurrent\_number 修改为 10,修改 partition 的offset,修改 group id。 `ALTER ROUTINE LOAD FOR db1.label1` `PROPERTIES` `(` `   "desired_concurrent_number" = "10"` `)` `FROM kafka` `(` `   "kafka_partitions" = "0, 1, 2",` `   "kafka_offsets" = "100, 200, 100",` `   "property.group.id" = "new_group"` `);` <br> #### 7.1.4.2 BROKER LOAD **说明** Broker load 通过随 DorisDB 集群一同部署的 broker 访问对应数据源的数据,进行数据导入。可以通过 show broker 命令查看已经部署的 broker。 <br> 目前支持以下3种数据源: (1)OSS:阿里云对象存储 (2)Apache HDFS:社区版本 HDFS。 (3)Amazon S3:Amazon对象存储。 语法定义如下: `LOAD LABEL load_label` `(data_desc1[, data_desc2, ...])` `WITH BROKER broker_name` `[broker_properties]` `[opt_properties];` **(1)load\_label** 当前导入批次的标签。在一个 database 内唯一。 `语法:` `   [database_name.]your_label` **(2)data\_desc** 用于描述一批导入数据。 `语法:` `   DATA INFILE` `   (` `   "file_path1"[, file_path2, ...]` `   )` `   [NEGATIVE]` `   INTO TABLE 'table_name'` `   [PARTITION (p1, p2)]` `   [COLUMNS TERMINATED BY "column_separator"]` `   [FORMAT AS "file_type"]` `   [(column_list)]` `   [SET (k1 = func(k2))]` `   [WHERE predicate]` * file\_path 文件路径,可以指定到一个文件,也可以用 \* 通配符指定某个目录下的所有文件。通配符必须匹配到文件,而不能是目录。 * PARTITION 如果指定此参数,则只会导入指定的分区,导入分区以外的数据会被过滤掉。如果不指定,默认导入table的所有分区。 * NEGATIVE 如果指定此参数,则相当于导入一批“负”数据。用于抵消之前导入的同一批数据。该参数仅适用于存在 value 列,并且 value 列的聚合类型仅为 SUM 的情况。 * column\_separator 用于指定导入文件中的列分隔符。默认为 \\t。如果是不可见字符,则需要加 \\\\x 作为前缀,使用十六进制来表示分隔符。如hive文件的分隔符\\x01,指定为"\\\\x01"。 * file\_type 用于指定导入文件的类型,例如:parquet、orc、csv。默认值通过文件后缀名判断。 * column\_list 用于指定导入文件中的列和 table 中的列的对应关系。当需要跳过导入文件中的某一列时,将该列指定为 table 中不存在的列名即可。 语法: `(col_name1, col_name2, ...)` * SET 如果指定此参数,可以将源文件某一列按照函数进行转化,然后将转化后的结果导入到table中。语法为column\_name = expression。举几个例子帮助理解。 <br> 例1:表中有3个列“c1, c2, c3", 源文件中前两列依次对应(c1,c2),后两列之和对应c3;那么需要指定 columns (c1,c2,tmp\_c3,tmp\_c4) SET (c3=tmp\_c3+tmp\_c4)。 <br> 例2:表中有3个列“year, month, day"三个列,源文件中只有一个时间列,为”2018-06-01 01:02:03“格式。那么可以指定columns(tmp\_time) set (year = year(tmp\_time), month=month(tmp\_time), day=day(tmp\_time))完成导入。 * WHERE 对做完 transform 的数据进行过滤,符合 WHERE 条件的数据才能被导入。WHERE 语句中只可引用表中列名。 **(3)broker\_name** 所使用的 broker 名称,可以通过 show broker 命令查看。 **(4)broker\_properties** 用于通过 broker 访问数据源的信息。不同的 broker,以及不同的访问方式,需要提供的信息不同。 (a)Apache HDFS        社区版本的 HDFS,支持简单认证、kerberos 认证。以及支持 HA 配置。 * 简单认证:        hadoop.security.authentication = simple (默认)        username:hdfs 用户名        password:hdfs 密码 * kerberos 认证:        hadoop.security.authentication = kerberos        kerberos\_principal:指定 kerberos 的 principal        kerberos\_keytab:指定 kerberos 的 keytab 文件路径。该文件必须为 broker 进程所在服务器上的文件。        kerberos\_keytab\_content:指定 kerberos 中 keytab 文件内容经过 base64 编码之后的内容。这个跟 kerberos\_keytab 配置二选一即可。 * namenode HA:        通过配置 namenode HA,可以在 namenode 切换时,自动识别到新的 namenode        dfs.nameservices: 指定 hdfs 服务的名字,自定义,如:"dfs.nameservices" = "my\_ha"        dfs.ha.namenodes.xxx:自定义 namenode 的名字,多个名字以逗号分隔。其中 xxx 为 dfs.nameservices 中自定义的名字,如 "dfs.ha.namenodes.my\_ha" = "my\_nn"        dfs.namenode.rpc-address.xxx.nn:指定 namenode 的rpc地址信息。其中 nn 表示 dfs.ha.namenodes.xxx 中配置的 namenode 的名字,如:"dfs.namenode.rpc-address.my\_ha.my\_nn" = "host:port"        dfs.client.failover.proxy.provider:指定 client 连接 namenode 的 provider,默认为:org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider (b)Amazon S3        需提供:        fs.s3a.access.key:AmazonS3的access key        fs.s3a.secret.key:AmazonS3的secret key        fs.s3a.endpoint:AmazonS3的endpoint **(5)opt\_properties** 用于指定一些特殊参数。 `语法:` `   [PROPERTIES ("key"="value", ...)]` `   可以指定如下参数:` `   timeout:         指定导入操作的超时时间。默认超时为4小时。单位秒。` `   max_filter_ratio:最大容忍可过滤(数据不规范等原因)的数据比例。默认零容忍。` `exec_mem_limit:  导入内存限制。默认为 2GB。单位为字节。` `strict mode:     是否对数据进行严格限制。默认为 false。` `timezone:         指定某些受时区影响的函数的时区,如 strftime/alignment_timestamp/from_unixtime 等等,具体请查阅 [时区] 文档。如果不指定,则使用 "Asia/Shanghai" 时区。` **(6)导入数据格式样例** * 整型类(TINYINT/SMALLINT/INT/BIGINT/LARGEINT):1, 1000, 1234 * 浮点类(FLOAT/DOUBLE/DECIMAL):1.1, 0.23, .356 * 日期类(DATE/DATETIME):2017-10-03, 2017-06-13 12:34:03。 > 注:如果是其他日期格式,可以在导入命令中,使用 strftime 或者 time\_format 函数进行转换。 * 字符串类(CHAR/VARCHAR):"I am a student", "a"。 * NULL值:\\N **示例** (1)从 HDFS 导入一批数据,指定超时时间和过滤比例。使用明文 my\_hdfs\_broker 的 broker。简单认证。 `LOAD LABEL example_db.label1` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/file")` `   INTO TABLE 'my_table'` `)` `WITH BROKER my_hdfs_broker` `(` `   "username" = "hdfs_user",` `   "password" = "hdfs_passwd"` `)` `PROPERTIES` `(` `   "timeout" = "3600",` `   "max_filter_ratio" = "0.1"` `);` 其中 hdfs\_host 为 namenode 的 host,hdfs\_port 为 fs.defaultFS 端口(默认9000)。 (2)从 AFS 一批数据,包含多个文件。导入不同的 table,指定分隔符,指定列对应关系。 `LOAD LABEL example_db.label2` `(` `   DATA INFILE("afs://afs_host:hdfs_port/user/dorisdb/data/input/file1")` `   INTO TABLE 'my_table_1'` `   COLUMNS TERMINATED BY "," (k1, k3, k2, v1, v2),` `   DATA INFILE("afs://afs_host:hdfs_port/user/dorisdb/data/input/file2")` `   INTO TABLE 'my_table_2'` `   COLUMNS TERMINATED BY "\t" (k1, k2, k3, v2, v1)` `)` `WITH BROKER my_afs_broker` `(` `   "username" = "afs_user",` `   "password" = "afs_passwd"` `)` `PROPERTIES` `(` `   "timeout" = "3600",` `   "max_filter_ratio" = "0.1"` `);` (3)从 HDFS 导入一批数据,指定hive的默认分隔符\\x01,并使用通配符\*指定目录下的所有文件。使用简单认证,同时配置 namenode HA。 `LOAD LABEL example_db.label3` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/*")` `   INTO TABLE 'my_table'` `   COLUMNS TERMINATED BY "\\x01"` `)` `WITH BROKER my_hdfs_broker` `(` `   "username" = "hdfs_user",` `   "password" = "hdfs_passwd",` `   "dfs.nameservices" = "my-ha",` `   "dfs.ha.namenodes.my_ha" = "my_namenode1, my_namenode2",` `   "dfs.namenode.rpc-address.my_ha.my_namenode1" = "nn1_host:rpc_port",` `   "dfs.namenode.rpc-address.my_ha.my_namenode2" = "nn2_host:rpc_port",` `   "dfs.client.failover.proxy.provider" = "org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider"` `);` (4)从 HDFS 导入一批“负”数据。同时使用 kerberos 认证方式。提供 keytab 文件路径。 `LOAD LABEL example_db.label4` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/old_file)` `   NEGATIVE` `   INTO TABLE 'my_table'` `   COLUMNS TERMINATED BY "\t"` `)` `WITH BROKER my_hdfs_broker` `(` `   "hadoop.security.authentication" = "kerberos",` `   "kerberos_principal"="doris@YOUR.COM",` `   "kerberos_keytab"="/home/dorisdb/dorisdb.keytab"` `);` (5)从 HDFS 导入一批数据,指定分区。同时使用 kerberos 认证方式。提供 base64 编码后的 keytab 文件内容。 `LOAD LABEL example_db.label5` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/file")` `   INTO TABLE 'my_table'` `   PARTITION (p1, p2)` `   COLUMNS TERMINATED BY "," (k1, k3, k2, v1, v2)` `)` `WITH BROKER my_hdfs_broker` `(` `   "hadoop.security.authentication"="kerberos",` `   "kerberos_principal"="doris@YOUR.COM",` `   "kerberos_keytab_content"="BQIAAABEAAEACUJBSURVLkNPTQAEcGFsbw"` `);` (6)从 BOS 导入一批数据,指定分区, 并对导入文件的列做一些转化,如下: `表结构为:` `   k1 varchar(20)` `   k2 int` `假设数据文件只有一行数据:` `   Adele,1,1` `数据文件中各列,对应导入语句中指定的各列:` `   k1,tmp_k2,tmp_k3` `转换如下:` `   1) k1: 不变换` `   2) k2:是 tmp_k2 和 tmp_k3 数据之和` `LOAD LABEL example_db.label6` `(` `   DATA INFILE("bos://my_bucket/input/file")` `   INTO TABLE 'my_table'` `   PARTITION (p1, p2)` `   COLUMNS TERMINATED BY "," (k1, tmp_k2, tmp_k3)` `   SET (` `     k2 = tmp_k2 + tmp_k3` `   )` `)` `WITH BROKER my_bos_broker` `(` `   "bos_endpoint" = "[http://bj.bcebos.com](http://bj.bcebos.com)",` `   "bos_accesskey" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",` `   "bos_secret_accesskey"="yyyyyyyyyyyyyyyyyyyy"` `);` (7)导入数据到含有HLL列的表,可以是表中的列或者新导入数据中的列。 设表中有4列分别是(id, v1, v2, v3),其中v1和v2列是hll列;导入的源文件有3列。(column\_list)中声明第一列为id,第二、三列为一个临时命名的k1、k2。在SET中必须给表中的hll列以特殊声明 hll\_hash。表中的v1列等于原始数据中的hll\_hash(k1)列, 表中的v3列在原始数据中并没有对应的值,使用empty\_hll补充默认值。 `LOAD LABEL example_db.label7` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/file")` `   INTO TABLE 'my_table'` `   PARTITION (p1, p2)` `   COLUMNS TERMINATED BY "," (id, k1, k2)` `   SET (` `     v1 = hll_hash(k1),` `     v2 = hll_hash(k2),` `     v3 = empty_hll()` `   )` `)` `WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");` `LOAD LABEL example_db.label8` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/file")` `   INTO TABLE 'my_table'` `   PARTITION (p1, p2)` `   COLUMNS TERMINATED BY "," (k1, k2, tmp_k3, tmp_k4, v1, v2)` `   SET (` `     v1 = hll_hash(tmp_k3),` `     v2 = hll_hash(tmp_k4)` `   )` `)` `WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");` (8)导入Parquet文件中数据  指定FORMAT 为parquet, 默认是通过文件后缀判断 `LOAD LABEL example_db.label9` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/file")` `   INTO TABLE 'my_table'` `   FORMAT AS "parquet"` `   (k1, k2, k3)` `)` `WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");` (9)提取文件路径中的分区字段 如果需要,则会根据表中定义的字段类型解析文件路径中的分区字段(partitioned fields),类似Spark中Partition Discovery的功能。 `LOAD LABEL example_db.label10` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/dir/city=beijing/*/*")` `   INTO TABLE 'my_table'` `   FORMAT AS "csv"` `   (k1, k2, k3)` `   COLUMNS FROM PATH AS (city, utc_date)` `   SET (uniq_id = md5sum(k1, city))` `)` `WITH BROKER hdfs ("username"="hdfs_user", "password"="hdfs_password");` hdfs://hdfs\_host:hdfs\_port/user/dorisdb/data/input/dir/city=beijing目录下包括如下文件: `[` `hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/dir/city=beijing/utc_date=2019-06-26/0000.csv, hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/dir/city=beijing/utc_date=2019-06-26/0001.csv,` `...` `]` 则提取文件路径的中的city和utc\_date字段。 (10)对待导入数据进行过滤,k1 值大于 k2 值的列才能被导入。 `LOAD LABEL example_db.label10` `(` `   DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/file")` `   INTO TABLE 'my_table'` `   where k1 > k2` `);` (11)从 AmazonS3 导入Parquet文件中数据,指定 FORMAT 为parquet,默认是通过文件后缀判断: `LOAD LABEL example_db.label11` `(` `   DATA INFILE("s3a://my_bucket/input/file")` `   INTO TABLE 'my_table'` `   FORMAT AS "parquet"` `   (k1, k2, k3)` `)` `WITH BROKER my_s3a_broker` `(` `   "fs.s3a.access.key" = "xxxxxxxxxxxxxxxxxxxxxxxxxx",` `   "fs.s3a.secret.key" = "yyyyyyyyyyyyyyyyyyyy",` `   "fs.s3a.endpoint" = "s3.amazonaws.com"` `);` (12)提取文件路径中的时间分区字段,并且时间包含 %3A (在 hdfs 路径中,不允许有 ':',所有 ':' 会由 %3A 替换)。 假设有如下文件: `/user/data/data_time=2020-02-17 00%3A00%3A00/test.txt` `/user/data/data_time=2020-02-18 00%3A00%3A00/test.txt` 表结构为: `data_time DATETIME,` `k2 INT,` `k3 INT` `LOAD LABEL example_db.label12` `(` `   DATA INFILE("hdfs://host:port/user/data/*/test.txt")` `   INTO TABLE 'tbl12'` `   COLUMNS TERMINATED BY ","` `   (k2,k3)` `   COLUMNS FROM PATH AS (data_time)` `   SET (data_time=str_to_date(data_time, '%Y-%m-%d %H%%3A%i%%3A%s'))` `)` `WITH BROKER "hdfs" ("username"="user", "password"="pass");` (13)从 HDFS 导入一批数据,指定超时时间和过滤比例。使用明文 my\_hdfs\_broker 的 broker。简单认证。并且将原有数据中与导入数据中 v2 大于 100 的行相匹配的行删除,其他行正常导入。 `LOAD LABEL example_db.label1` `(` `   MERGE DATA INFILE("hdfs://hdfs_host:hdfs_port/user/dorisdb/data/input/file")` `   INTO TABLE 'my_table'` `   COLUMNS TERMINATED BY "\t"` `   (k1, k2, k3, v2, v1)` `)` `DELETE ON v2 >100` `WITH BROKER my_hdfs_broker` `(` `   "username" = "hdfs_user",` `   "password" = "hdfs_passwd"` `)` `PROPERTIES` `(` `   "timeout" = "3600",` `   "max_filter_ratio" = "0.1"` `);` (14)导入时指定source\_sequence列,保证UNIQUE\_KEYS表中的替换顺序。 `LOAD LABEL example_db.label_sequence` `(` `   DATA INFILE("hdfs://host:port/user/data/*/test.txt")` `   INTO TABLE 'tbl1'` `   COLUMNS TERMINATED BY ","` `   (k1,k2,source_sequence,v1,v2)` `   ORDER BY source_sequence` `)` `with BROKER "hdfs" ("username"="user", "password"="pass");` <br> #### 7.1.4.3 CANCEL LOAD **说明** 该语句用于撤销指定 load label 的批次的导入作业。这是一个异步操作,任务提交成功则返回。执行后可使用 SHOW LOAD 命令查看进度。 `语法:` `CANCEL LOAD` `   [FROM db_name]` `   WHERE LABEL = "load_label";` <br> **示例** 撤销数据库 example\_db 上, label 为 example\_db\_test\_load\_label 的导入作业。 `CANCEL LOAD` `FROM example_db` `WHERE LABEL = "example_db_test_load_label";` <br> #### 7.1.4.4 DELETE **说明** 该语句用于按条件删除指定 table (base index) partition 中的数据。该操作会同时删除和此 base index 相关的 rollup index 的数据。 `语法:` `   DELETE FROM table_name [PARTITION partition_name]` `   WHERE` `column_name1 op { value | value_list } [ AND column_name2 op { value | value_list } ...];` `说明:` `   1) op 的可选类型包括:=, >, <, >=, <=, !=, in` `   2) 只能指定 key 列上的条件。` `   3) 当选定的 key 列不存在于某个 rollup 中时,无法进行 delete。` `   4) 条件之间只能是“与”的关系。` `      若希望达成“或”的关系,需要将条件分写在两个 DELETE 语句中。` `注意:` `   该语句可能会降低执行后一段时间内的查询效率。` `   影响程度取决于语句中指定的删除条件的数量。` `   指定的条件越多,影响越大。` **示例** (1)删除 my\_table partition p1 中 k1 列值为 3 的数据行 `DELETE FROM my_table PARTITION p1` `WHERE k1 = 3;` (2)删除 my\_table partition p1 中 k1 列值大于等于 3 且 k2 列值为 "abc" 的数据行 `DELETE FROM my_table PARTITION p1` `WHERE k1 >= 3 AND k2 = "abc";` (3)删除 my\_table 所有分区中 k2 列值为 "abc" 或者 "cba" 的数据行 `DELETE FROM my_table` `WHERE k2 in ("abc", "cba");` <br> #### 7.1.4.5 EXPORT **说明** 该语句用于将指定表的数据导出到指定位置。详见《数据导出》。 该功能通过 broker 进程实现。对于不同的目的存储系统,需要部署不同的 broker。可以通过 SHOW BROKER 查看已部署的 broker。 这是一个异步操作,任务提交成功则返回。执行后可使用 SHOW EXPORT 命令查看进度。 `语法:` `EXPORT TABLE table_name` `   [PARTITION (p1[,p2])]` `   TO export_path` `   [opt_properties]` `   broker;` **(1)table\_name**  当前要导出的表的表名,目前支持engine为olap和mysql的表的导出。 **(2)partition**  可以只导出指定表的某些指定分区。 **(3)export\_path**  导出的路径,需为目录。目前不能导出到本地,需要导出到broker。 **(4)opt\_properties**  用于指定一些特殊参数。 `语法:` `   [PROPERTIES ("key"="value", ...)]` `可以指定如下参数:` `   column_separator: 指定导出的列分隔符,默认为\t。` `   line_delimiter: 指定导出的行分隔符,默认为\n。` `exec_mem_limit: 导出在单个 BE 节点的内存使用上限,默认为 2GB,单位为字节。` `   timeout:导出作业的超时时间,默认为1天,单位是秒。` `   tablet_num_per_task:每个子任务能分配的最大 Tablet 数量。` <br> **(5)broker** 用于指定导出使用的broker。语法: `WITH BROKER broker_name ("key"="value"[,...])` 这里需要指定具体的broker name, 以及所需的broker属性。对于不同存储系统对应的 broker,这里需要输入的参数不同。具体可以参阅:help broker load 中 broker 所需属性。 <br> **示例** (1)将 testTbl 表中的所有数据导出到 HDFS 上 `EXPORT TABLE testTbl TO "hdfs://hdfs_host:port/a/b/c" WITH BROKER "broker_name" ("username"="xxx", "password"="yyy");` (2)将 testTbl 表中的分区p1,p2导出到 HDFS 上 `EXPORT TABLE testTbl PARTITION (p1,p2) TO "hdfs://hdfs_host:port/a/b/c" WITH BROKER "broker_name" ("username"="xxx", "password"="yyy");` (3)将 testTbl 表中的所有数据导出到 hdfs 上,以","作为列分隔符 `EXPORT TABLE testTbl TO "hdfs://hdfs_host:port/a/b/c" PROPERTIES ("column_separator"=",") WITH BROKER "broker_name" ("username"="xxx", "password"="yyy");` <br> #### 7.1.4.6 GROUP BY **说明** GROUP BY GROUPING SETS | CUBE | ROLLUP 是对 GROUP BY 子句的扩展,它能够在一个 GROUP BY 子句中实现多个集合的分组聚合。其结果等价于将多个相应 GROUP BY 子句进行 UNION 操作。 <br> GROUP BY 子句是只含有一个元素的 GROUP BY GROUPING SETS 的特例。 例如,GROUPING SETS 语句: `SELECT a, b, SUM( c ) FROM tab1 GROUP BY 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;` GROUPING(expr)指示一个列是否为聚合列,如果是聚合列为0,否则为1。 <br> GROUPING\_ID(expr \[ , expr \[ , ... \] \]) 与GROUPING 类似,GROUPING\_ID根据指定的column 顺序,计算出一个列列表的 Bitmap 值,每一位为GROUPING的值。 GROUPING\_ID()函数返回位向量的十进制值。 <br> GROUP BY语法定义如下: `SELECT ...` `FROM ...` `[ ... ]` `GROUP BY [` `   , ... |` `   GROUPING SETS [, ...] (  groupSet [ , groupSet [ , ... ] ] ) |` `   ROLLUP(expr  [ , expr [ , ... ] ]) |` `   expr  [ , expr [ , ... ] ] WITH ROLLUP |` `   CUBE(expr  [ , expr [ , ... ] ]) |` `   expr  [ , expr [ , ... ] ] WITH CUBE` `   ]` `[ ... ]` * groupSet 表示 select list 中的列,别名或者表达式组成的集合 groupSet ::= { ( expr \[ , expr \[ , ... \] \] )}。 * expr 表示 select list 中的列,别名或者表达式。 DorisDB 也支持类似 PostgreSQL 语法,语法实例如下: `SELECT a, b, SUM( c ) FROM tab1 GROUP BY GROUPING SETS ( (a, b), (a), (b), ( ) );` `SELECT a, b,c, SUM( d ) FROM tab1 GROUP BY ROLLUP(a,b,c)` `SELECT a, b,c, SUM( d ) FROM tab1 GROUP BY CUBE(a,b,c)` 其中,ROLLUP(a,b,c) 等价于如下GROUPING SETS 语句: `GROUPING SETS (` `(a,b,c),` `( a, b ),` `( a),` `( )` `)` CUBE ( a, b, c ) 等价于如下GROUPING SETS 语句: `GROUPING SETS (` `( a, b, c ),` `( a, b ),` `( a,    c ),` `( a ),` `(    b, c ),` `(    b ),` `( c ),` `(         )` `)` <br> **示例** `> SELECT * FROM t;` `+------+------+------+` `| k1   | k2 | k3   |` `+------+------+------+` `| a    | A|    1 |` `| a    | A|    2 |` `| a    | B|    1 |` `| a    | B|    3 |` `| b    | A|    1 |` `| b    | A|    4 |` `| b    | B|    1 |` `| b    | B|    5 |` `+------+------+------+` `8 rows in set (0.01 sec)` `> SELECT k1, k2, SUM(k3) FROM t GROUP BY GROUPING SETS ( (k1, k2), (k2), (k1), ( ) );` `+------+------+-----------+` `| k1   | k2 | sum('k3') |` `+------+------+-----------+` `| b    | B|         6 |` `| a    | B|         4 |` `| a    | A|         3 |` `| b    | A|         5 |` `| NULL | B|        10 |` `| NULL | A|         8 |` `| a    | NULL |         7 |` `| b    | NULL |        11 |` `| NULL | NULL |        18 |` `+------+------+-----------+` `9 rows in set (0.06 sec)` `> SELECT k1, k2, GROUPING_ID(k1,k2), SUM(k3) FROM t GROUP BY GROUPING SETS ((k1, k2), (k1), (k2), ());` `+------+------+---------------+----------------+` `| k1   | k2 | grouping_id(k1,k2) | sum('k3') |` `+------+------+---------------+----------------+` `| a    | A|             0 |              3 |` `| a    | B|             0 |              4 |` `| a    | NULL |             1 |              7 |` `| b    | A|             0 |              5 |` `| b    | B|             0 |              6 |` `| b    | NULL |             1 |             11 |` `| NULL | A|             2 |              8 |` `| NULL | B|             2 |             10 |` `| NULL | NULL |             3 |             18 |` `+------+------+---------------+----------------+` `9 rows in set (0.02 sec)` <br> #### 7.1.4.7 INSERT **说明** 该语句用于向表中插入数据,是DorisDB支持的数据导入方式之一(详见《数据导入》中关于Insert Into的说明)。语法: `INSERT INTO table_name` `   [ PARTITION (p1, ...) ]` `   [ WITH LABEL label]` `   [ (column [, ...]) ]` `   [ [ hint [, ...] ] ]` `   { VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }` * tablet\_name:导入数据的目的表。可以是 db\_name.table\_name 形式。 * partitions:指定待导入的分区,必须是 table\_name 中存在的分区,多个分区名称用逗号分隔。 * label:为 Insert 任务指定一个 label。 * column\_name:指定的目的列,必须是 table\_name 中存在的列。 * expression:需要赋值给某个列的对应表达式。 * DEFAULT:让对应列使用默认值。 * query:一个普通查询,查询的结果会写入到目标中。 * hint:用于指示 INSERT 执行行为的一些指示符。streaming 和默认的非 streaming 方式均会使用同步方式完成 INSERT 语句执行。非 streaming 方式在执行完成后会返回一个 label 方便用户通过 SHOW LOAD 查询导入的状态。 当前执行 INSERT 语句时,对于有不符合目标表格式的数据,默认的行为是过滤,比如字符串超长等。但是对于有要求数据不能够被过滤的业务场景,可以通过设置会话变量 enable\_insert\_strict为 true 来确保当有数据被过滤掉的时候,INSERT 不会被成功执行。 <br> **示例** 表test 包含两个列c1, c2。 (1)向test表中导入一行数据。 `INSERT INTO test VALUES (1, 2);` `INSERT INTO test (c1, c2) VALUES (1, 2);` `INSERT INTO test (c1, c2) VALUES (1, DEFAULT);` `INSERT INTO test (c1) VALUES (1);` 其中第一条、第二条语句是一样的效果。在不指定目标列时,使用表中的列顺序来作为默认的目标列。 第三条、第四条语句表达的意思是一样的,使用c2列的默认值,来完成数据导入。 (2)向test表中一次性导入多行数据。 `INSERT INTO test VALUES (1, 2), (3, 2 + 2);` `INSERT INTO test (c1, c2) VALUES (1, 2), (3, 2 + 2);` `INSERT INTO test (c1) VALUES (1), (3);` `INSERT INTO test (c1, c2) VALUES (1, DEFAULT), (3, DEFAULT);` 其中第一条、第二条语句效果一样,向test表中一次性导入两条数据。第三条、第四条语句效果一致,使用c2列的默认值向test表中导入两条数据。 (3)向 test 表中导入一个查询语句的结果。 `INSERT INTO test SELECT * FROM test2;` `INSERT INTO test (c1, c2) SELECT * from test2;` (4)向test 表中导入一个查询语句的结果,并指定 partition 和 label。 `INSERT INTO test PARTITION(p1, p2) WITH LABEL `label1` SELECT * FROM test2;` `INSERT INTO test WITH LABEL `label1` (c1, c2) SELECT * from test2;` <br> #### 7.1.4.8 PAUSE ROUTINE LOAD **说明** 暂停指定的例行导入作业。 `PAUSE ROUTINE LOAD FOR [db.]job_name;` <br> **示例** 暂停名称为 test1 的例行导入作业。 `PAUSE ROUTINE LOAD FOR test1;` <br> #### 7.1.4.9 RESUME ROUTINE LOAD **说明** 恢复被暂停的例行导入作业。 `RESUME ROUTINE LOAD FOR [db.]job_name;` **示例** 恢复名称为 test1 的例行导入作业。 `RESUME ROUTINE LOAD FOR test1;` <br> #### 7.1.4.10 ROUTINE LOAD **说明** 例行导入(Routine Load)功能,支持用户提交一个常驻的导入任务,通过不断的从指定的数据源读取数据,将数据导入到 DorisDB 中。目前仅支持通过无认证或者 SSL 认证方式从 Kakfa 导入文本格式(CSV)的数据。 详见《数据导入》中关于 Routine Load 的说明。 <br> #### 7.1.4.11 SELECT Select语句由select,from,where,group by,having,order by,union等部分组成,DorisDB的查询语句基本符合SQL92标准,下面详细介绍支持的select用法。 连接(Join) 连接操作是合并2个或多个表的数据,然后返回其中某些表中的某些列的结果集。目前DorisDB支持inner join,outer join,semi join,anti join,cross join。在inner join条件里除了支持等值join,还支持不等值join,为了性能考虑,推荐使用等值join。其它join只支持等值join。连接的语法定义如下: `SELECT select_list FROM` `        table_or_subquery1 [INNER] JOIN table_or_subquery2 |` `        table_or_subquery1 {LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]} JOIN table_or_subquery2 |` `        table_or_subquery1 {LEFT | RIGHT} SEMI JOIN table_or_subquery2 |` `        table_or_subquery1 {LEFT | RIGHT} ANTI JOIN table_or_subquery2 |` `                [ ON col1 = col2 [AND col3 = col4 ...] |` `                          USING (col1 [, col2 ...]) ]` `        [other_join_clause ...]` `        [ WHERE where_clauses ]` `SELECT select_list FROM` `        table_or_subquery1, table_or_subquery2 [, table_or_subquery3 ...]` `        [other_join_clause ...]` `WHERE` `        col1 = col2 [AND col3 = col4 ...]` `SELECT select_list FROM` `        table_or_subquery1 CROSS JOIN table_or_subquery2` `        [other_join_clause ...]` `[ WHERE where_clauses ]` <br> **Self-Join** DorisDB支持self-joins,即自己和自己join。例如同一张表的不同列进行join。实际上没有特殊的语法标识self-join。self-join中join两边的条件都来自同一张表,我们需要给他们分配不同的别名。例如: `SELECT lhs.id, rhs.parent, lhs.c1, rhs.c2 FROM tree_data lhs, tree_data rhs WHERE lhs.id = rhs.parent;` <br> **笛卡尔积(Cross Join)** Cross join会产生大量的结果,须慎用cross join,即使需要使用cross join时也需要使用过滤条件并且确保返回结果数较少。例如: `SELECT * FROM t1, t2;` `SELECT * FROM t1 CROSS JOIN t2;` <br> **Inner join** Inner join 是大家最熟知,最常用的join。返回的结果来自相近的2张表所请求的列,join 的条件为两个表的列包含有相同的值。如果两个表的某个列名相同,我们需要使用全名(table\_name.column\_name形式)或者给列名起别名。例如: `-- 下列3个查询是等价的。` `SELECT t1.id, c1, c2 FROM t1, t2 WHERE t1.id = t2.id;` `SELECT t1.id, c1, c2 FROM t1 JOIN t2 ON t1.id = t2.id;` `SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;` <br> **Outer join** Outer join返回左表或者右表或者两者所有的行。如果在另一张表中没有匹配的数据,则将其设置为NULL。例如: `SELECT * FROM t1 LEFT OUTER JOIN t2 ON t1.id = t2.id;` `SELECT * FROM t1 RIGHT OUTER JOIN t2 ON t1.id = t2.id;` `SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;` <br> **等值和不等值join** 通常情况下,用户使用等值join居多,等值join要求join条件的操作符是等号。不等值join在join条件上可以使用!=,等符号。不等值join会产生大量的结果,在计算过程中可能超过内存限额,因此需要谨慎使用。不等值join只支持inner join。例如: `SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id = t2.id;` `SELECT t1.id, c1, c2 FROM t1 INNER JOIN t2 ON t1.id > t2.id;` <br> **Semi join** Left semi join只返回左表中能匹配右表数据的行,不管能匹配右表多少行数据,左表的该行最多只返回一次。Right semi join原理相似,只是返回的数据是右表的。例如: `SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT SEMI JOIN t2 ON t1.id = t2.id;` <br> **Anti join** Left anti join只返回左表中不能匹配右表的行。Right anti join反转了这个比较,只返回右表中不能匹配左表的行。例如: `SELECT t1.c1, t1.c2, t1.c2 FROM t1 LEFT ANTI JOIN t2 ON t1.id = t2.id;` Order by Order by通过比较一列或者多列的大小来对结果集进行排序。order by是比较耗时耗资源的操作,因为所有数据都需要发送到1个节点后才能排序,排序操作相比不排序操作需要更多的内存。如果需要返回前N个排序结果,需要使用LIMIT从句;为了限制内存的使用,如果用户没有指定LIMIT从句,则默认返回前65535个排序结果。Order by语法定义如下: `ORDER BY col [ASC | DESC]` 默认排序顺序是ASC(升序)。示例: `select * from big_table order by tiny_column, short_column desc;` Group by Group by从句通常和聚合函数(例如COUNT(), SUM(), AVG(), MIN()和MAX())一起使用。Group by指定的列不会参加聚合操作。Group by从句可以加入Having从句来过滤聚合函数产出的结果。例如: `mysql> select tiny_column, sum(short_column) from small_table group by tiny_column;` `+-------------+---------------------+` `| tiny_column | sum('short_column') |` `+-------------+---------------------+` `|           1 |                   2 |` `|           2 |                   1 |` `+-------------+---------------------+` `2 rows in set (0.07 sec)` Having Having从句不是过滤表中的行数据,而是过滤聚合函数产出的结果。通常来说having要和聚合函数(例如COUNT(), SUM(), AVG(), MIN(), MAX())以及group by从句一起使用。示例: `mysql> select tiny_column, sum(short_column) from small_table group by tiny_column having sum(short_column) = 1;` `+-------------+---------------------+` `| tiny_column | sum('short_column') |` `+-------------+---------------------+` `|           2 |                   1 |` `+-------------+---------------------+` `1 row in set (0.07 sec)` `mysql> select tiny_column, sum(short_column) from small_table group by tiny_column having tiny_column > 1;` `+-------------+---------------------+` `| tiny_column | sum('short_column') |` `+-------------+---------------------+` `|           2 |                   1 |` `+-------------+---------------------+` `1 row in set (0.07 sec)` Limit Limit从句用于限制返回结果的最大行数。设置返回结果的最大行数可以帮助DorisDB优化内存的使用。该从句主要应用如下场景: * 返回top-N的查询结果。 * 想简单看下表中包含的内容。 * 表中数据量大,或者where从句没有过滤太多的数据,需要限制查询结果集的大小。 使用说明:limit从句的值必须是数字型字面常量。 举例: `mysql> select tiny_column from small_table limit 1;` `+-------------+` `| tiny_column |` `+-------------+` `| 1 |` `+-------------+` `1 row in set (0.02 sec)` `mysql> select tiny_column from small_table limit 10000;` `+-------------+` `| tiny_column |` `+-------------+` `| 1 |` `| 2 |` `+-------------+` `2 rows in set (0.01 sec)` Offset Offset从句使得结果集跳过前若干行结果后直接返回后续的结果。结果集默认起始行为第0行,因此offset 0和不带offset返回相同的结果。通常来说,offset从句需要与order by从句和limit从句一起使用才有效。示例: `mysql> select varchar_column from big_table order by varchar_column limit 3;` `+----------------+` `| varchar_column |` `+----------------+` `| beijing        |` `| chongqing      |` `| tianjin        |` `+----------------+` `3 rows in set (0.02 sec)` `mysql> select varchar_column from big_table order by varchar_column limit 1 offset 0;` `+----------------+` `| varchar_column |` `+----------------+` `| beijing        |` `+----------------+` `1 row in set (0.01 sec)` `mysql> select varchar_column from big_table order by varchar_column limit 1 offset 1;` `+----------------+` `| varchar_column |` `+----------------+` `| chongqing      |` `+----------------+` `1 row in set (0.01 sec)` `mysql> select varchar_column from big_table order by varchar_column limit 1 offset 2;` `+----------------+` `| varchar_column |` `+----------------+` `| tianjin        |` `+----------------+` `1 row in set (0.02 sec)` > 注:在没有order by的情况下使用offset语法是允许的,但是此时offset无意义,这种情况只取limit的值,忽略掉offset的值。因此在没有order by的情况下,offset超过结果集的最大行数依然是有结果的。建议用户使用offset时一定要带上order by。 Union Union从句用于合并多个查询的结果集。语法定义如下: `query_1 UNION [DISTINCT | ALL] query_2` <br> 使用说明: 只使用union关键词和使用union disitnct的效果是相同的。由于去重工作是比较耗费内存的,因此使用union all操作查询速度会快些,耗费内存会少些。如果用户想对返回结果集进行order by和limit操作,需要将union操作放在子查询中,然后select from subquery,最后把subquery和order by放在子查询外面。 <br> 举例: `mysql> (select tiny_column from small_table) union all (select tiny_column from small_table);` `+-------------+` `| tiny_column |` `+-------------+` `| 1 |` `| 2 |` `| 1 |` `| 2 |` `+-------------+` `4 rows in set (0.10 sec)` `mysql> (select tiny_column from small_table) union (select tiny_column from small_table);` `+-------------+` `| tiny_column |` `+-------------+` `| 2 |` `| 1 |` `+-------------+` `2 rows in set (0.11 sec)` `mysql> select * from (select tiny_column from small_table union all\` `-> select tiny_column from small_table) as t1 \` `-> order by tiny_column limit 4;` `+-------------+` `| tiny_column |` `+-------------+` `| 1 |` `| 1 |` `| 2 |` `| 2 |` `+-------------+` `4 rows in set (0.11 sec)` Distinct Distinct操作符对结果集进行去重。示例: `-- Returns the unique values from one column.` `select distinct tiny_column from big_table limit 2;` `-- Returns the unique combinations of values from multiple columns.` `select distinct tiny_column, int_column from big_table limit 2;` distinct可以和聚合函数(通常是count函数)一同使用,count(disitnct)用于计算出一个列或多个列上包含多少不同的组合。 `mysql> -- Counts the unique values from one column.` `mysql> select count(distinct tiny_column) from small_table;` `+-------------------------------+` `| count(DISTINCT 'tiny_column') |` `+-------------------------------+` `| 2 |` `+-------------------------------+` `1 row in set (0.06 sec)` `mysql> -- Counts the unique combinations of values from multiple columns.` `mysql> select count(distinct tiny_column, int_column) from big_table limit 2;` DorisDB支持多个聚合函数同时使用distinct。 `mysql> -- Count the unique value from multiple aggregation function separately.` `mysql> select count(distinct tiny_column, int_column), count(distinct varchar_column) from big_table;` <br> **子查询** 子查询按相关性分为不相关子查询和相关子查询。 <br> **不相关子查询** 不相关子查询支持\[NOT\] IN和EXISTS。举例: `SELECT x FROM t1 WHERE x [NOT] IN (SELECT y FROM t2);` `SELECT x FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE y = 1);` <br> **相关子查询** 相关子查询支持\[NOT\] IN和\[NOT\] EXISTS。举例: `SELECT * FROM t1 WHERE x [NOT] IN (SELECT a FROM t2 WHERE t1.y = t2.b);` `SELECT * FROM t1 WHERE [NOT] EXISTS (SELECT a FROM t2 WHERE t1.y = t2.b);` 子查询还支持标量子查询。分为不相关标量子查询、相关标量子查询和标量子查询作为普通函数的参数。举例: `-- 1) 不相关标量子查询,谓词为=号。例如输出最高工资的人的信息。` `SELECT name FROM table WHERE salary = (SELECT MAX(salary) FROM table);` `-- 2) 不相关标量子查询,谓词为>,<等。例如输出比平均工资高的人的信息。` `SELECT name FROM table WHERE salary > (SELECT AVG(salary) FROM table);` `-- 3) 相关标量子查询。例如输出各个部门工资最高的信息。` `SELECT name FROM table a WHERE salary = (SELECT MAX(salary) FROM table b WHERE b.部门= a.部门);` `-- 4) 标量子查询作为普通函数的参数。` `SELECT name FROM table WHERE salary = abs((SELECT MAX(salary) FROM table));` With子句 可以在SELECT语句之前添加的子句,用于定义在SELECT内部多次引用的复杂表达式的别名。与CREATE VIEW类似,但在子句中定义的表和列名在查询结束后不会持久,也不会与实际表或VIEW中的名称冲突。 用WITH子句的好处有: 1. 方便和易于维护,减少查询内部的重复。 2. 通过将查询中最复杂的部分抽象成单独的块,更易于阅读和理解SQL代码。 举例: `-- Define one subquery at the outer level, and another at the inner level as part of the` `-- initial stage of the UNION ALL query.` `with t1 as (select 1) (with t2 as (select 2) select * from t2) union all select * from t1;` Where与操作符 SQL操作符是一系列用于比较的函数,这些操作符广泛的用于select 语句的where从句中。 **算数操作符** 算术操作符通常出现在包含左操作数,操作符,右操作数(大部分情况下)组成的表达式中。 * +和-:可以作为单元或2元操作符。当其作为单元操作符时,如+1, -2.5 或者-col\_name, 表达的意思是该值乘以+1或者-1。因此单元操作符+返回的是未发生变化的值,单元操作符-改变了该值的符号位。用户可以将两个单元操作符叠加起来,比如++5(返回的是正值),-+2 或者+-2(这两种情况返回的是负值),但是用户不能使用连续的两个-号,因为--被解释为后面的语句是注释(用户在是可以使用两个-号的,此时需要在两个-号之间加上空格或圆括号,如-(-2)或者- -2,这两个数实际表达的结果是+2)。+或者-作为2元操作符时,例如2+2,3+1.5 或者col1 + col2,表达的含义是左值相应的加或者减去右值。左值和右值必须都是数字类型。 * \*和/: 分别代表着乘法和除法。两侧的操作数必须都是数据类型。当两个数相乘时,类型较小的操作数在需要的情况下类型可能会提升(比如SMALLINT提升到INT或者BIGINT 等),表达式的结果被提升到下一个较大的类型,比如TINYINT 乘以INT 产生的结果的类型会是BIGINT)。当两个数相乘时,为了避免精度丢失,操作数和表达式结果都会被解释成DOUBLE 类型。如果用户想把表达式结果转换成其他类型,需要用CAST 函数转换。 * %:取模操作符。返回左操作数除以右操作数的余数。左操作数和右操作数都必须是整型。 * &,|和^:按位操作符返回对两个操作数进行按位与,按位或,按位异或操作的结果。两个操作数都要求是一种整型类型。如果按位操作符的两个操作数的类型不一致,则类型小的操作数会被提升到类型较大的操作数,然后再做相应的按位操作。在1个表达式中可以出现多个算术操作符,用户可以用小括号将相应的算术表达式括起来。算术操作符通常没有对应的数学函数来表达和算术操作符相同的功能。比如我们没有MOD()函数来表示%操作符的功能。反过来,数学函数也没有对应的算术操作符。比如幂函数POW()并没有相应的 \*\*求幂操作符。用户可以通过数学函数章节了解我们支持哪些算术函数。 **Between操作符** 在where从句中,表达式可能同时与上界和下界比较。如果表达式大于等于下界,同时小于等于上界,比较的结果是true。语法定义如下: `expression BETWEEN lower_bound AND upper_bound` 数据类型:通常表达式(expression)的计算结果都是数字类型,该操作符也支持其他数据类型。如果必须要确保下界和上界都是可比较的字符,可以使用cast()函数。 <br> 使用说明:如果操作数是string类型应注意,起始部分为上界的长字符串将不会匹配上界,该字符串比上界要大。例如:"between 'A' and 'M' "不会匹配‘MJ’。如果需要确保表达式能够正常工作,可以使用一些函数,如upper(), lower(), substr(), trim()。 举例: `mysql> select c1 from t1 where month between 1 and 6;` **比较操作符** 比较操作符用来判断列和列是否相等或者对列进行排序。=, !=, , >=可以适用所有数据类型。其中<>符号是不等于的意思,与!=的功能一致。IN和BETWEEN操作符提供更简短的表达来描述相等、小于、大小等关系的比较。 **In操作符** In操作符会和VALUE集合进行比较,如果可以匹配该集合中任何一元素,则返回TRUE。参数和VALUE集合必须是可比较的。所有使用IN操作符的表达式都可以写成用OR连接的等值比较,但是IN的语法更简单,更精准,更容易让DorisDB进行优化。 举例: `mysql> select * from small_table where tiny_column in (1,2);` **Like操作符** 该操作符用于和字符串进行比较。"\_"用来匹配单个字符,"%"用来匹配多个字符。参数必须要匹配完整的字符串。通常,把"%"放在字符串的尾部更加符合实际用法。 举例: `mysql> select varchar_column from small_table where varchar_column like 'm%';` `+----------------+` `| varchar_column |` `+----------------+` `| milan          |` `+----------------+` `1 row in set (0.02 sec)` `mysql> select varchar_column from small_table where varchar_column like 'm____';` `+----------------+` `| varchar_column |` `+----------------+` `| milan          |` `+----------------+` `1 row in set (0.01 sec)` <br> **逻辑操作符** 逻辑操作符返回一个BOOL值,逻辑操作符包括单元操作符和多元操作符,每个操作符处理的参数都是返回值为BOOL值的表达式。支持的操作符有: * AND: 2元操作符,如果左侧和右侧的参数的计算结果都是TRUE,则AND操作符返回TRUE。 * OR: 2元操作符,如果左侧和右侧的参数的计算结果有一个为TRUE,则OR操作符返回TRUE。如果两个参数都是FALSE,则OR操作符返回FALSE。 * NOT:单元操作符,反转表达式的结果。如果参数为TRUE,则该操作符返回FALSE;如果参数为FALSE,则该操作符返回TRUE。 举例: `mysql> select true and true;` `+-------------------+` `| (TRUE) AND (TRUE) |` `+-------------------+` `| 1 |` `+-------------------+` `1 row in set (0.00 sec)` `mysql> select true and false;` `+--------------------+` `| (TRUE) AND (FALSE) |` `+--------------------+` `|                  0 |` `+--------------------+` `1 row in set (0.01 sec)` `mysql> select true or false;` `+-------------------+` `| (TRUE) OR (FALSE) |` `+-------------------+` `| 1 |` `+-------------------+` `1 row in set (0.01 sec)` `mysql> select not true;` `+----------+` `| NOT TRUE |` `+----------+` `|        0 |` `+----------+` `1 row in set (0.01 sec)` **正则表达式操作符** 判断是否匹配正则表达式。使用POSIX标准的正则表达式,"^"用来匹配字符串的首部,"$"用来匹配字符串的尾部,"."匹配任何一个单字符,"\*"匹配0个或多个选项,"+"匹配1个多个选项,"?"表示分贪婪表示等等。正则表达式需要匹配完整的值,并不是仅仅匹配字符串的部分内容。如果想匹配中间的部分,正则表达式的前面部分可以写成"^.\*" 或者".\*"。"^"和"$"通常是可以省略的。RLKIE操作符和REGEXP操作符是同义词。"|"操作符是个可选操作符,"|"两侧的正则表达式只需满足1侧条件即可,"|"操作符和两侧的正则表达式通常需要用()括起来。 举例: `mysql> select varchar_column from small_table where varchar_column regexp '(mi|MI).*';` `+----------------+` `| varchar_column |` `+----------------+` `| milan          |` `+----------------+` `1 row in set (0.01 sec)` `mysql> select varchar_column from small_table where varchar_column regexp 'm.*';` `+----------------+` `| varchar_column |` `+----------------+` `| milan          |` `+----------------+` `1 row in set (0.01 sec)` <br> 别名 当在查询中书写表、列,或者包含列的表达式的名字时,可以同时给它们分配一个别名。当需要使用表名、列名时,可以使用别名来访问。别名通常相对原名来说更简短更好记。当需要新建一个别名时,只需在select list或者from list中的表、列、表达式名称后面加上AS alias从句即可。AS关键词是可选的,用户可以直接在原名后面指定别名。如果别名或者其他标志符和内部关键词同名时,需要在该名称加上``符号。别名对大小写是敏感的。 举例: `mysql> select tiny_column as name, int_column as sex from big_table;` `mysql> select sum(tiny_column) as total_count from big_table;` `mysql> select one.tiny_column, two.int_column from small_table one, <br> big_table two where one.tiny_column = two.tiny_column;` <br> #### 7.1.4.12 SHOW ALTER **说明** 该语句用于展示当前正在进行的各类修改任务的执行情况。 `语法:` `   SHOW ALTER [CLUSTER | TABLE [COLUMN | ROLLUP] [FROM db_name]];` `说明:` `TABLE COLUMN:展示修改列的 ALTER 任务。` `                 支持语法[WHERE TableName|CreateTime|FinishTime|State] [ORDER BY] [LIMIT]` `   TABLE ROLLUP:展示创建或删除 ROLLUP index 的任务` `   如果不指定 db_name,使用当前默认 db` `   CLUSTER: 展示集群操作相关任务情况(仅管理员使用!待实现...)` **示例** (1)展示默认 db 的所有修改列的任务执行情况 `SHOW ALTER TABLE COLUMN;` (2)展示某个表最近一次修改列的任务执行情况 `SHOW ALTER TABLE COLUMN WHERE TableName = "table1" ORDER BY CreateTime DESC LIMIT 1;` (3)展示指定 db 的创建或删除 ROLLUP index 的任务执行情况 `SHOW ALTER TABLE ROLLUP FROM example_db;` (4)展示集群操作相关任务(要求管理员权限) `SHOW ALTER CLUSTER;` <br> #### 7.1.4.13 SHOW BACKUP **说明** 该语句用于查看 BACKUP 任务。 `语法:` `   SHOW BACKUP [FROM db_name]` `说明:` `1) DorisDB 中仅保存最近一次 BACKUP 任务。` `   2) 各列含义如下:` `       JobId:                  唯一作业id` `       SnapshotName:           备份的名称` `       DbName:                 所属数据库` `       State:                  当前阶段` `           PENDING:        提交作业后的初始状态` `           SNAPSHOTING:    执行快照中` `           UPLOAD_SNAPSHOT:快照完成,准备上传` `           UPLOADING:      快照上传中` `           SAVE_META:      将作业元信息保存为本地文件` `           UPLOAD_INFO:    上传作业元信息` `           FINISHED:       作业成功` `           CANCELLED:      作业失败` `       BackupObjs:          备份的表和分区` `       CreateTime:          任务提交时间` `       SnapshotFinishedTime:快照完成时间` `       UploadFinishedTime:  快照上传完成时间` `       FinishedTime:        作业结束时间` `       UnfinishedTasks:     在SNAPSHOTING和UPLOADING阶段会显示还未完成的子任务id` `       Status:              如果作业失败,显示失败信息` `       Timeout:             作业超时时间,单位秒` <br> **示例** 查看 example\_db 下最后一次 BACKUP 任务。 `SHOW BACKUP FROM example_db;` <br> #### 7.1.4.14 SHOW DATA **说明** 该语句用于展示数据量、副本数量以及统计行数。语法定义如下: `SHOW DATA [FROM db_name[.table_name]];` 说明: (1)如果不指定 FROM 子句,则展示当前 db 下细分到各个 table 的数据量和副本数量。其中数据量为**所有副本的总数据量**。而副本数量为表的所有分区以及所有物化视图的副本数。并且是**压缩后的数据量**。 (2)如果指定 FROM 子句,则展示 table 下细分到各个物化视图的数据量、副本数量和统计行数。其中数据量为所有副本的总数据量。副本数量为对应物化视图的所有分区的副本数量。统计行数为对应物化视图的所有分区统计行数。 (3)统计行数时,以多个副本中行数最大的那个副本为准。 (4)结果集中的 Total 行表示汇总行。Quota 行表示当前数据库设置的配额。Left 行表示剩余配额。 (5)如果想查看各个 Partition 的大小,请参阅 help show partitions。 **示例** (1)展示默认 db 中各个 table 的数据量,副本数量,汇总数据量和汇总副本数量。 `SHOW DATA;` `+-----------+-------------+--------------+` `| TableName | Size| ReplicaCount |` `+-----------+-------------+--------------+` `| tbl1      | 900.000 B | 6            |` `| tbl2      | 500.000 B | 3            |` `| Total     | 1.400 KB| 9            |` `| Quota     | 1024.000 GB | 1073741824   |` `| Left      | 1021.921 GB | 1073741815   |` `+-----------+-------------+--------------+` (2)展示指定 db 中指定表的细分数据量、副本数量和统计行数。 `SHOW DATA FROM example_db.test;` `+-----------+-----------+-----------+--------------+----------+` `| TableName | IndexName | Size      | ReplicaCount | RowCount |` `+-----------+-----------+-----------+--------------+----------+` `| test      | r1| 10.000MB  | 30           | 10000    |` `|           | r2| 20.000MB  | 30           | 20000    |` `|           | test2 | 50.000MB  | 30           | 50000    |` `|           | Total | 80.000    | 90           |          |` `+-----------+-----------+-----------+--------------+----------+` <br> #### 7.1.4.15 SHOW DATABASES **说明** 该语句用于展示当前可见的 db。 `SHOW DATABASES;` <br> #### 7.1.4.16 SHOW DELETE **说明** 该语句用于展示已执行成功的历史 delete 任务。 `SHOW DELETE [FROM db_name]` **示例** 展示数据库 test\_db 的所有历史 delete 任务。 `SHOW DELETE FROM test_db;` <br> #### 7.1.4.17 SHOW DYNAMIC PARTITION TABLES **说明** 该语句用于展示当前db下所有的动态分区表状态。 `SHOW DYNAMIC PARTITION TABLES [FROM db_name];` **示例** 展示数据库 test\_db 的所有动态分区表状态。 `   SHOW DYNAMIC PARTITION TABLES FROM test_db;` <br> #### 7.1.4.18 SHOW EXPORT **说明** 该语句用于展示指定的导出任务的执行情况。 `语法:` `   SHOW EXPORT` `   [FROM db_name]` `   [` `       WHERE` `       [EXPORT_JOB_ID = your_job_id]` `       [STATE = ["PENDING"|"EXPORTING"|"FINISHED"|"CANCELLED"]]` `   ]` `   [ORDER BY ...]` `   [LIMIT limit];` `说明:` `   1) 如果不指定 db_name,使用当前默认db。` `   2) 如果指定了 STATE,则匹配 EXPORT 状态。` `   3) 可以使用 ORDER BY 对任意列组合进行排序。` `   4) 如果指定了 LIMIT,则显示 limit 条匹配记录。否则全部显示。` **示例** (1)展示默认库的所有导出任务。 `SHOW EXPORT;` (2)展示指定库的导出任务,按 StartTime 降序排序。 `SHOW EXPORT FROM example_db ORDER BY StartTime DESC;` (3)展示指定库的导出任务,state 为 "exporting", 并按 StartTime 降序排序。 `SHOW EXPORT FROM example_db WHERE STATE = "exporting" ORDER BY StartTime DESC;` (4)展示指定数据库、指定job\_id指向的导出任务。 `SHOW EXPORT FROM example_db WHERE EXPORT_JOB_ID = job_id;` <br> #### 7.1.4.19 SHOW LOAD **说明** 该语句用于展示指定的导入任务的执行情况。 `语法:` `   SHOW LOAD` `   [FROM db_name]` `   [` `       WHERE` `       [LABEL [ = "your_label" | LIKE "label_matcher"]]` `       [STATE = ["PENDING"|"ETL"|"LOADING"|"FINISHED"|"CANCELLED"|]]` `   ]` `   [ORDER BY ...]` `   [LIMIT limit][OFFSET offset];` `说明:` `   1) 如果不指定 db_name,使用当前默认db。` `   2) 如果使用 LABEL LIKE,则会匹配导入任务的 label 包含 label_matcher 的导入任务。` `   3) 如果使用 LABEL = ,则精确匹配指定的 label。` `4) 如果指定了 STATE,则匹配 LOAD 状态。` `   5) 可以使用 ORDER BY 对任意列组合进行排序。` `   6) 如果指定了 LIMIT,则显示 limit 条匹配记录,否则全部显示。` `   7) 如果指定了 OFFSET,则从偏移量offset开始显示查询结果。默认情况下偏移量为0。` `8) 如果是使用 broker/mini load,则 URL 列中的连接可以使用以下命令查看:` `       SHOW LOAD WARNINGS ON 'url'` **示例** (1)展示默认 db 的所有导入任务 `SHOW LOAD;` (2)展示指定 db 的导入任务,label 中包含字符串 "2014\_01\_02",展示最老的10个 `SHOW LOAD FROM example_db WHERE LABEL LIKE "2014_01_02" LIMIT 10;    ` (3)展示指定 db 的导入任务,指定 label 为 "load\_example\_db\_20140102" 并按 LoadStartTime 降序排序 `SHOW LOAD FROM example_db WHERE LABEL = "load_example_db_20140102" ORDER BY LoadStartTime DESC;` (4)展示指定 db 的导入任务,指定 label 为 "load\_example\_db\_20140102" ,state 为 "loading", 并按 LoadStartTime 降序排序 `SHOW LOAD FROM example_db WHERE LABEL = "load_example_db_20140102" AND STATE = "loading" ORDER BY LoadStartTime DESC;` (5)展示指定 db 的导入任务 并按 LoadStartTime 降序排序,并从偏移量5开始显示10条查询结果 `SHOW LOAD FROM example_db ORDER BY LoadStartTime DESC limit 5,10;` `SHOW LOAD FROM example_db ORDER BY LoadStartTime DESC limit 10 offset 5;` (6)小批量导入是查看导入状态的命令 `curl --location-trusted -u {user}:{passwd} http://{hostname}:{port}/api/{database}/_load_info?label={labelname}` <br> #### 7.1.4.20 SHOW PARTITIONS **说明** 该语句用于展示分区信息。 `语法:` `   SHOW PARTITIONS FROM [db_name.]table_name [WHERE] [ORDER BY] [LIMIT];` `说明:` `   支持PartitionId, PartitionName, State, Buckets, ReplicationNum, LastConsistencyCheckTime 等列的过滤。` **示例** (1)展示指定db下指定表的所有分区信息 `SHOW PARTITIONS FROM example_db.table_name;` (2)展示指定db下指定表的指定分区的信息 `SHOW PARTITIONS FROM example_db.table_name WHERE PartitionName = "p1";` (3)展示指定db下指定表的最新分区的信息 `SHOW PARTITIONS FROM example_db.table_name ORDER BY PartitionId DESC LIMIT 1;` <br> #### 7.1.4.21 SHOW PROPERTY **说明** 该语句用于查看用户的属性。 `SHOW PROPERTY [FOR user] [LIKE key]` **示例** (1)查看 jack 用户的属性 `SHOW PROPERTY FOR 'jack'` (2)查看 jack 用户导入cluster相关属性 `SHOW PROPERTY FOR 'jack' LIKE '%load_cluster%'` <br> #### 7.1.4.22 SHOW REPOSITORIES **说明** 该语句用于查看当前已创建的仓库。 `语法:` `   SHOW REPOSITORIES;` `输出结果的各列含义如下:` `   RepoId:     唯一的仓库ID` `   RepoName:   仓库名称` `   CreateTime: 第一次创建该仓库的时间` `   IsReadOnly: 是否为只读仓库` `   Location:   仓库中用于备份数据的根目录` `   Broker:     依赖的 Broker` `   ErrMsg:     DorisDB 会定期检查仓库的连通性,如果出现问题,这里会显示错误信息` <br> #### 7.1.4.23 SHOW RESTORE **说明** 该语句用于查看 RESTORE 任务。 `语法:` `   SHOW RESTORE [FROM db_name]` `说明:` `1) Drois 仅保存最近一次 RESTORE 任务。` `   2) 各列含义如下:` `       JobId:                  唯一作业id` `       Label:                  要恢复的备份的名称` `       Timestamp:              要恢复的备份的时间版本` `       DbName:                 所属数据库` `       State:                  当前阶段` `           PENDING:        提交作业后的初始状态` `           SNAPSHOTING:    执行快照中` `           DOWNLOAD:       快照完成,准备下载仓库中的快照` `           DOWNLOADING:    快照下载中` `           COMMIT:         快照下载完成,准备生效` `           COMMITING:      生效中` `           FINISHED:       作业成功` `           CANCELLED:      作业失败` `       AllowLoad:              恢复时是否允许导入(当前不支持)` `       ReplicationNum:         指定恢复的副本数` `       RestoreJobs:            要恢复的表和分区` `       CreateTime:             任务提交时间` `       MetaPreparedTime:       元数据准备完成时间` `       SnapshotFinishedTime:   快照完成时间` `       DownloadFinishedTime:   快照下载完成时间` `       FinishedTime:           作业结束时间` `       UnfinishedTasks:        在 SNAPSHOTING、DOWNLOADING 和 COMMITING 阶段会显示还未完成的子任务id` `       Status:                 如果作业失败,显示失败信息` `       Timeout:                作业超时时间,单位秒` <br> **示例** 查看 example\_db 中最近一次 RESTORE 任务的信息。 `SHOW RESTORE FROM example_db;` <br> #### 7.1.4.24 SHOW ROUTINE LOAD **说明** 展示例行导入作业的信息。详见《数据导入》。 `# 显示 [database] 下,所有的例行导入作业(包括已停止或取消的作业)。结果为一行或多行。` `SHOW ALL ROUTINE LOAD;` `# 显示 [database] 下,名称为 job_name 的当前正在运行的例行导入作业。` `SHOW ROUTINE LOAD FOR [database.][job_name];` **示例** (1)展示名称为 test1 的所有例行导入作业(包括已停止或取消的作业)。结果为一行或多行。 `SHOW ALL ROUTINE LOAD FOR test1;` (2)展示名称为 test1 的当前正在运行的例行导入作业 `SHOW ROUTINE LOAD FOR test1;` (3)显示 example\_db 下,所有的例行导入作业(包括已停止或取消的作业)。结果为一行或多行。 `use example_db; SHOW ALL ROUTINE LOAD;` (4)显示 example\_db 下,所有正在运行的例行导入作业 `use example_db; SHOW ROUTINE LOAD;` (5)显示 example\_db 下,名称为 test1 的当前正在运行的例行导入作业 `SHOW ROUTINE LOAD FOR example_db.test1;` (6)显示 example\_db 下,名称为 test1 的所有例行导入作业(包括已停止或取消的作业)。结果为一行或多行。 `SHOW ALL ROUTINE LOAD FOR example_db.test1;` <br> #### 7.1.4.25 SHOW ROUTINE LOAD TASK **示例** 展示例行导入任务(test1)的子任务信息。 `SHOW ROUTINE LOAD TASK WHERE JobName = "test1";` <br> #### 7.1.4.26 SHOW SNAPSHOT **说明** 该语句用于查看仓库中已存在的备份。 `语法:` `   SHOW SNAPSHOT ON 'repo_name'` `   [WHERE SNAPSHOT = "snapshot" [AND TIMESTAMP = "backup_timestamp"]];` `说明:` `   1) 各列含义如下:` `       Snapshot:   备份的名称` `       Timestamp:  对应备份的时间版本` `       Status:     如果备份正常,则显示 OK,否则显示错误信息` `   2) 如果指定了 TIMESTAMP,则会额外显示如下信息:` `       Database:   备份数据原属的数据库名称` `       Details:    以 Json 的形式,展示整个备份的数据目录及文件结构` **示例** (1)查看仓库 example\_repo 中已有的备份: `SHOW SNAPSHOT ON example_repo;` (2)仅查看仓库 example\_repo 中名称为 backup1 的备份: `SHOW SNAPSHOT ON example_repo WHERE SNAPSHOT = "backup1";` (3)查看仓库 example\_repo 中名称为 backup1 的备份,时间版本为 "2018-05-05-15-34-26" 的详细信息: `SHOW SNAPSHOT ON example_repo` `WHERE SNAPSHOT = "backup1" AND TIMESTAMP = "2018-05-05-15-34-26";` <br> #### 7.1.4.27 SHOW TABLES **说明** 该语句用于展示当前数据库中所有的表。 `SHOW TABLES;` <br> #### 7.1.4.28 SHOW TABLET **说明** 该语句用于显示 tablet 相关的信息(仅管理员使用)。 `语法:` `   SHOW TABLET` `   [FROM [db_name.]table_name | tablet_id] [partiton(partition_name_1, partition_name_1)]` `   [where [version=1] [and backendid=10000] [and state="NORMAL|ROLLUP|CLONE|DECOMMISSION"]]` `   [order by order_column]` `   [limit [offset,]size]` show tablet命令支持按照按照以下字段进行过滤:partition, index name, version, backendid, state,同时支持按照任意字段进行排序,并且提供limit限制返回条数。 **示例** (1)显示指定 db 的下指定表所有 tablet 信息 `SHOW TABLET FROM example_db.table_name;` `// 获取partition p1和p2的tablet信息` `SHOW TABLET FROM example_db.table_name partition(p1, p2);` `// 获取10个结果` `SHOW TABLET FROM example_db.table_name limit 10;` `// 从偏移5开始获取10个结果` `SHOW TABLET FROM example_db.table_name limit 5,10;` `// 按照backendid/version/state字段进行过滤` `SHOW TABLET FROM example_db.table_name where backendid=10000 and version=1 and state="NORMAL";` `// 按照version字段进行排序` `SHOW TABLET FROM example_db.table_name where backendid=10000 order by version;` `// 获取index名字为t1_rollup的tablet相关信息` `SHOW TABLET FROM example_db.table_name where indexname="t1_rollup";` (2)显示指定 tablet id 为 10000 的 tablet 的父层级 id 信息 `SHOW TABLET 10000;` <br> #### 7.1.4.29 SHOW TRANSACTION **说明** 该语法用于查看指定事务的详情。语法定义如下: `SHOW TRANSACTION` `[FROM db_name]` `WHERE id = transaction_id;` 返回结果示例: `TransactionId: 4005` `            Label: insert_8d807d5d-bcdd-46eb-be6d-3fa87aa4952d` `Coordinator: FE: 10.74.167.16` `TransactionStatus: VISIBLE` `LoadJobSourceType: INSERT_STREAMING` `PrepareTime: 2020-01-09 14:59:07` `CommitTime: 2020-01-09 14:59:09` `FinishTime: 2020-01-09 14:59:09` `           Reason:` `ErrorReplicasCount: 0` `ListenerId: -1` `TimeoutMs: 300000` 返回结果各项的含义如下。 * TransactionId:事务id * Label:导入任务对应的 label * Coordinator:负责事务协调的节点 * TransactionStatus:事务状态 * PREPARE:准备阶段 * COMMITTED:事务成功,但数据不可见 * VISIBLE:事务成功且数据可见 * ABORTED:事务失败 * LoadJobSourceType:导入任务的类型。 * PrepareTime:事务开始时间 * CommitTime:事务提交成功的时间 * FinishTime:数据可见的时间 * Reason:错误信息 * ErrorReplicasCount:有错误的副本数 * ListenerId:相关的导入作业的id * TimeoutMs:事务超时时间,单位毫秒 <br> #### 7.1.4.30 SPARK LOAD **说明** Spark load 通过外部的 Spark 资源实现对导入数据的预处理,提高 DorisDB 大数据量的导入性能并且节省 DorisDB 集群的计算资源。主要用于初次迁移,大数据量导入 DorisDB 的场景。 <br> Spark load 是一种异步导入方式,用户需要通过 MySQL 协议创建 Spark 类型导入任务,并通过 SHOW LOAD 命令查看导入结果。详见《数据导入》中关于Spark Load的说明。 <br> #### 7.1.4.31 STOP ROUTINE LOAD **说明** 停止指定的例行导入作业。 `STOP ROUTINE LOAD FOR [db.]job_name;` **示例** 停止名称为 test1 的例行导入作业。 `STOP ROUTINE LOAD FOR test1;` <br> #### 7.1.4.32 STREAM LOAD **说明** 该语句用于向指定的 table 导入数据。数据来源则是本地CSV文件。详见[数据导入](4.数据导入.md)中关于Stream Load的说明。