3.103.173.75.2.15.4.1

JOBS

EXPORT

使用EXPORT导出表格数据到CSV文件中。

所需权限

需要用户拥有admin权限。

注意：

此语句不支持事务。

建议导出为每个节点都可以访问到的公共文件。

从hubble中导出数据

以下示例nodelocal://1/ ，其中数字1代表在第一个节点进行操作，且推荐。

export into 
csv 'nodelocal://1/cust' 
with delimiter ='|',nullas=''
from 
select * from tt_image1;

常用的分隔符有\t或者\001等，以\001为例

数据准备：

create table cust_info(
    cust_no       string primary key,
    cust_name     varchar(30) not null,
    cust_card_no  varchar(18),  
    cust_phoneno  decimal(15),
    cust_address  varchar(30),
    cust_type     varchar(10),
    index(cust_card_no)
 );
 
insert into cust_info values('14435550','王吉','12022519960321531X',15122511874,'天津武清','抵押');
insert into cust_info values('14435551','张贺','431256197306265320',15534343555,null,'质押');
...

nullas参数来定义字段表示过滤null,比如：以上第二条insert语句中地址字段为空值

export into 
csv 'nodelocal://1/cust' 
with delimiter =e'\001',nullas=''
from 
select * from cust_info;
                     filename                                      | rows |   bytes
-------------------------------------------------------------------+------+------------
  export17665471189f3d580000000000000001-n871817396986347523.0.csv |  115 | 121347951

对应的导入语句见下文import栏目

注意：在处理更多行之前上传文件大小的默认值为32M，为减少导出的csv数量，加入参数chunk_size,建议chunk_size='512M'

export into 
csv 'nodelocal://1/cust' 
with delimiter ='|' ,chunk_size='512M'
from 
select * from tt_image1;

返回结果：文件数据大，可能会导出多个csv

                     filename                     | rows |   bytes
--------------------------------------------------+------+------------
  export16d82d3d6101475c0000000000000001-n3.0.csv |   15 | 121347951
  export16d82d3d6101475c0000000000000001-n4.0.csv |   44 | 355954016

IMPORT

  • 用于将表格数据导入到单个表中，无法在事务中使用。

  • 成功启动导入后，它将导入注册为job，你可以使用SHOW JOBS查看。

  • 导入开会后，你可以使用PAUSE JOB,RESUME JOB和CANCEL JOB来控制它。

  • 暂停然后恢复IMPORT作业将导致它从头开始重新启动。

所需权限

只有root用户才能运行IMPORT任务。

注意 使用http搭建文件服务，必须使各节点可以访问到待导入文件 Csv的导入：因为目录结尾是*.csv,所以目录下只能放本张表导出的csv

导入一：img表存在的情况，先创建表，然后将数据导入

import into img ( a , b  
) csv data ( 'nodelocal://1/customers/*.csv' ) 
with delimiter = '|'
;
返回结果：

       job_id       |  status   | fraction_completed | rows | index_entries | system_records |   bytes    
+--------------------+-----------+--------------------+------+---------------+----------------+-----------+
  739023580166127617 | succeeded |                  1 |   59 |             0 |              0 | 238652345

分隔符\001为例，将数据进行导入

nullif参数防止导入的null值都变成空字符串

import into cust_info1 CSV DATA(
'nodelocal://1/cust/*.csv')WITH delimiter = e'\001',nullif = e'';
       job_id        |  status   | fraction_completed | rows | index_entries |   bytes    
+------------------- +-----------+--------------------+------+---------------+-----------+
  871818819657760769 | succeeded |                  1 |  115 |             5 | 238652345

导入二：表不存在的情况

import table  img ( a int  , b  bytes
) csv data ( 'nodelocal://1/customers/*.csv' ) 
with delimiter = '|';
返回结果：

       job_id       |  status   | fraction_completed | rows | index_entries | system_records |   bytes    
+--------------------+-----------+--------------------+------+---------------+----------------+-----------+
  739024174094942209 | succeeded |                  1 |   59 |             0 |              0 | 238652345

在数据导入的过程中，字段中有可能存在特殊字符，如果字段存在有双引号(例如:"北京")或者字段有转义字符(例如:shanghai\pudong)，导入时候可以参考以下参数：

  • fields_terminated_by用于单个字符的分割
  • fields_enclosed_by 用于将字段的内容包装起来的符号的情况，例如:双引号
  • fields_escaped_by用于字段中包含的转义字符的情况，例如:\

如下一张表：

create table test (id int,cust_address string,aml string);

文件test.txt里面的样例数据如下：

1,"beijing",qwe\123
2,"shanghai",env\456
import into  test delimited data  ('nodelocal://1/test.txt') 
with fields_terminated_by = e',', fields_enclosed_by='"', fields_escaped_by='\';
        job_id       |  status   | fraction_completed | rows | index_entries | bytes
---------------------+-----------+--------------------+------+---------------+--------
  779219645387079681 | succeeded |                  1 |    2 |             0 |    78

SHOW JOBS

查看集群中所有长时间运行的任务，用于获取到影响集群性能的关键任务，从而帮助用户来控制任务状态。

所需权限

仅root用户执行SHOW JOBS语句

SHOW JOBS;

可将SHOW JOBS作为SELECT语句的数据源用于过滤job，通过WHERE条件来查找需要的job信息。

SELECT * FROM [ SHOW JOBS ] 
WHERE
    job_type = 'RESTORE' 
    AND status IN ( 'running', 'succeeded' ) 
ORDER BY
    created DESC;

CANCEL JOB

用于停止长时间运行的job，包括IMPORT、BACKUP与RESTORE任务。取消RESTORE后，将清理部分还原的数据。可能会对集群性能产生短暂的较小的影响。

所需权限

默认情况下，只有root用户可以使用CANCEL JOB

停止单个任务

CANCEL JOB <job_id>;

停止多个任务

CANCEL JOBS (SELECT job_id FROM [SHOW JOBS] WHERE user_name = 'beagledata');

PAUSE JOB

PAUSE JOB语句可以暂停的IMPORT、BACKUP、RESTORE、USER-CREATED TABLESTATISTICS JOBS和AUTOMATICTABLE STATISTICS JOBS任务。

注意

无法暂停schema的修改

所需权限

用户需要root权限

暂停单个任务

PAUSE JOB <job_id>;

暂停多个任务

PAUSE JOBS (SELECT job_id FROM [SHOW JOBS] WHERE user_name = 'beagledata');

RESUME JOB

RESUME JOB语句可以恢复已暂停的IMPORT、BACKUP或RESTORE任务。

所需权限

用户需要root权限

恢复单个JOB

RESUME job <job_id>;

恢复多个JOB

RESUME JOBS (SELECT job_id FROM [SHOW JOBS] WHERE user_name = 'beagledata');

EXPLAIN

EXPLAIN说明

语句返回指定语句的查询计划，用户可以根据explain语句优化查询。

查询优化

使用EXPLAIN输出，可以按如下方式优化查询：

  • 具有较少数据的查询条件执行得更快。
  • 避免扫描整个表，这是访问数据最慢的方式。创建至少包含查询在其子句中过滤的列之一的索引。

可以通过以下方式了解查询是否正在执行整个表扫描EXPLAIN：

  • 查询使用的索引；显示为table的值。
  • 正在扫描索引中的键值；显示为spans的值。

参数介绍

参数详情
VERBOSE尽可能多地显示有关声明计划的信息。
TYPES选择评估中间 SQL 表达式的中间数据类型。
OPT显示由基于成本的优化器生成的语句计划树。
VEC显示有关查询的矢量化执行计划的详细信息。
DISTSQL生成一个指向分布式 SQL 物理语句计划图的 URL。
preparable_stmt想要了解详细信息的语句。所有可准备的陈述都是可以解释的。

返回信息

描述详情
全局属性适用于整个查询计划的属性。全局属性包括distribution和vectorized。
报表计划树属性报表计划层次结构的树表示。
指数推荐：N索引建议的数量，后跟索引操作列表和执行操作的 SQL 语句。
时间查询的时间详细信息，总时间是查询的计划和执行时间。执行时间是最终语句计划完成所用的时间，网络时间是在集群中的相关节点上分发查询所花费的时间。有些查询不需要分布式，所以网络时间为0ms。

定位索引和键范围

可以使用EXPLAIN来了解查询使用哪些索引和键范围，这可以确定查询没有执行全表扫描。

create table shops (aml int primary key, num int);
  • 用非索引列查询
explain select  * from shops where num between 4 and 6;
                                       info
-----------------------------------------------------------------------------------
  distribution: full
  vectorized: false

  • filter
  │ estimated row count: 1
  │ filter: (num >= 4) AND (num <= 6)
  │
  └── • scan
        estimated row count: 1 (100% of the table; stats collected 2 seconds ago)
        table: shops@primary
        spans: FULL SCAN
(11 rows)

说明：重点查看spans值，因为列num不是索引列，进行过滤的查询会扫描整个表。

  • 用索引列查询
explain select  * from shops where aml between 80 and 100;
                                   info
-----------------------------------------------------------------------------
  distribution: local
  vectorized: false

  • scan
    estimated row count: 1 (100% of the table; stats collected 6 hours ago)
    table: shops@primary
    spans: [/80 - /100]

说明：重点查看spans值，因为列aml是索引列，进行过滤的查询语句避免了全表扫描。

单表执行样例

explain select * from bptfhist   where tx_tm>151111  order by tx_tm asc;
                                              info
------------------------------------------------------------------------------------------------------
  distribution: full
  vectorized: true

  • sort
  │ estimated row count: 40,854
  │ order: +tx_tm
  │
  └── • filter
      │ estimated row count: 40,854
      │ filter: tx_tm > 151111
      │
      └── • scan
            estimated row count: 100,000 (100% of the table; stats collected 24 days ago)
            table: bptfhist@primary
            spans: FULL SCAN
  • distribution：full

计划者选择了一个分布式执行计划，其中查询的执行由多个节点并行执行，然后由网关节点返回结果。具有full分布的执行计划不会在集群中的所有节点上处理。local仅在网关节点上执行具有分布的执行计划。即使执行计划是local，也可能从远程节点获取行数据，但数据的处理由本地节点执行。

  • vectorized：true

该计划将使用矢量化执行引擎执行。

  • estimated row count：100,000 (100% of the table; stats collected 24 days ago)

查询扫描的估计行数，在本例中100000为数据行；查询跨越的表的百分比，在本例中为100%；以及上次收集表的统计信息的时间。

  • order：tx_tm

排序将在revenue列上按升序排列。

  • filter：tx_tm > 151111

列上的扫描过滤器revenue。

  • table：bptfhist@primary

在索引上扫描表。

  • spans：FULL SCAN

该表在索引的所有键范围上进行即全表扫描。

表联合执行样例

explain select  * from test_trans as t   join bptfhist as b on b.ac = t.ac;
 distribution: full
  vectorized: false

  • lookup join
  │ table: bptfhist@primary
  │ equality: (rowid) = (rowid)
  │ equality cols are key
  │
  └── • lookup join
      │ estimated row count: 1
      │ table: bptfhist@idx_bptfhist3
      │ equality: (ac) = (ac)
      │
      └── • scan
            estimated row count: 1 (100% of the table; stats collected 17 minutes ago)
            table: test_trans@primary
            spans: FULL SCAN

备注：相比于单表查询equality输出显示查询将执行的连接条件。

EXPLAIN ANALYZE

EXPLAIN ANALYZE说明

EXPLAIN ANALYZE语句的工作方式类似于EXPLAIN，主要区别在于前者实际上会执行语句。这样可以将查询计划中的估计值与执行时所遇到的实际值进行比较。

参数介绍

参数详情
PLAN执行该语句并返回一个带有可解释语句的计划和执行时间的语句计划。
DISTSQL执行语句并返回语句计划和性能统计信息以及生成的指向图形分布式 SQL物理语句计划树的链接。
DEBUG执行该语句并生成一个 ZIP 文件，其中包含有关查询和查询中引用的数据库对象的详细信息的文件。
preparable_stmt您要执行和分析的语句。所有可准备的陈述都是可以解释的。

备注：默认情况下，EXPLAIN ANALYZE使用该PLAN选项，EXPLAIN ANALYZE与EXPLAIN ANALYZE (PLAN)产生相同的输出，也是常用的。

返回信息

一条成功的EXPLAIN ANALYZE语句会返回一个表，列中包含以下详细信息：

参数详情
全局属性适用于整个语句计划的属性和统计信息。
报表计划树属性报表计划层次结构的树表示。
节点详细信息树中当前语句计划节点的属性、列和排序详细信息。
时间声明的时间详细信息。总时间是语句的计划和执行时间。执行时间是最终语句计划完成所用的时间。网络时间是在集群中的相关节点之间分发语句所花费的时间。有些语句不需要分发，所以网络时间为0ms。

语句分析重点是全局属性和报表计划树属性两类信息的查看

全局属性

参数详情
planning time计划者创建报表计划所花费的总时间。
execution time完成最终执行计划所需的时间。
distribution声明是分发的还是本地的。如果distribution是full，则语句​​的执行由多个节点并行执行，则结果由网关节点返回。如果local，则仅在网关节点上执行执行计划。即使执行计划是local，也可能从远程节点获取行数据，但数据的处理由本地节点执行
vectorized指示此语句中是否使用了向量化执行引擎
rows read from KV从存储层读取的行数
cumulative time spent in KV在存储层中花费的总时间
maximum memory usage此语句在执行期间随时使用的最大内存量
network usage执行语句时通过网络传输的数据量。如果值为 0 B，则该语句在单个节点上执行并且没有使用网络
regions受影响节点所在的区域
max sql temp disk usage(仅DISTSQL参数)执行查询时发生多少磁盘溢出。仅当磁盘使用率大于零时才显示此属性

报表计划树属性

参数详情
processor语句计划层次结构中的每个处理器都有一个节点，其中包含有关语句该阶段的详细信息。如有GROUP BY子句的语句有一个group处理器，其中包含有关集群节点、行和与操作相关的GROUP BY操作的详细信息
nodes受此阶段语句影响的集群节点名称
regions受影响节点所在的区域
actual row count执行期间受此处理器影响的实际行数
KV time此阶段语句在存储层中的总时间
KV contention time在此声明阶段中存储层争用的时间
KV rows read在扫描期间，此阶段语句读取的存储层中的行数。
KV bytes read在扫描期间，在语句的这个阶段从存储层读取的数据量
estimated max memory allocated为语句估计的最大分配内存
estimated max sql temp disk usage语句的估计最大临时磁盘使用量
estimated row count根据语句计划器估计受此处理器影响的行数、查询跨越的表的百分比以及上次收集表的统计信息的时间
table语句中的扫描操作中使用的表和索引，格式为{table name}@{index name}
spans处理器读取密钥空间的间隔，如果spans是FULL SCAN，则在索引的所有键范围上扫描该表。如果spans是，则处理器仅读取[/1 - /1]具有值的键

EXPLAIN ANALYZE

EXPLAIN ANALYZE语句执行一个查询

explain analyze  select jrnno, avg(tx_tm) from bptfhist group by jrnno;

以下展示统计的信息：

                                         info
---------------------------------------------------------------------------------------
  planning time: 614µs
  execution time: 307ms
  distribution: full
  vectorized: true
  rows read from KV: 100,000 (76 MiB)
  cumulative time spent in KV: 235ms
  maximum memory usage: 16 MiB
  network usage: 384 B (3 messages)
  regions: ch-beijin

  • group
  │ nodes: n4
  │ regions: ch-beijin
  │ actual row count: 4
  │ estimated row count: 4
  │ group by: jrnno
  │
  └── • scan
        nodes: n4
        regions: ch-beijin
        actual row count: 100,000
        KV rows read: 100,000
        KV bytes read: 76 MiB
        estimated row count: 100,000 (100% of the table; stats collected 24 days ago)
        table: bptfhist@primary
        spans: FULL SCAN

说明：主要关注以下信息：

  • execution time

完成最终执行计划所需的时间

  • distribution

计划者选择了一个分布式执行计划，其中查询的执行由多个节点并行执行，然后由网关节点返回结果。具有full分布的执行计划不会在集群中的所有节点上处理。local仅在网关节点上执行具有分布的执行计划。即使执行计划是local，也可能从远程节点获取行数据，但数据的处理由本地节点执行。

  • vectorized

该执行是否使用矢量化执行引擎执行。

  • rows read from KV

从存储层读取的行数

  • cumulative time spent in KV

在存储层中花费的总时间

  • maximum memory usage

此语句在执行期间随时使用的最大内存量

  • network usage

执行语句时通过网络传输的数据量。如果值为 0 B，则该语句在单个节点上执行并且没有使用网络

  • actual row count

执行期间受此处理器影响的实际行数

  • KV rows read

在扫描期间，此阶段语句读取的存储层中的行数

  • KV bytes read

在扫描期间，在语句的这个阶段从存储层读取的数据量

  • estimated max memory allocated

为语句估计的最大分配内存

  • estimated max sql temp disk usage

语句的估计最大临时磁盘使用量

  • table

语句中的扫描操作中使用的表和索引，格式为{table name}@{index name}

  • spans

处理器读取密钥空间的间隔。如果spans是FULL SCAN，则在索引的所有键范围上扫描该表。如果spans是[/1 - /1]，则处理器读取范围键的值是[/1 - /1]

通过监控页面分析sql执行情况

1.登录页面后，点击语句“语句列表”，页面可以清晰的查看到每个SQL的基本

sqlExplain

2.选中执行的SQL语句，点击进去，可以具体查看SQL的概况、诊断、逻辑计划及其执行状态

sqlview

Copyright © 2024 天云融创数据科技（北京）有限公司京ICP备15014755号-3