云数据仓库ADB有什么使用技巧吗？

云数据仓库AnalyticDB（简称ADB）提供了多种使用技巧，可以帮助用户更高效地管理和查询数据。以下是根据知识库资料整理的详细使用技巧：

1. OSS Foreign Table 使用技巧

OSS Foreign Table 是 ADB 中用于访问存储在阿里云对象存储服务（OSS）上的外部数据的重要功能。以下是一些关键操作技巧： - 查看执行计划
使用 EXPLAIN 命令查看查询语句的执行计划，帮助优化查询性能。例如：

EXPLAIN SELECT COUNT(*) FROM oss_lineitem_orc WHERE l_orderkey > 14062498;

• 收集统计信息
  OSS Foreign Table 默认不会自动收集统计信息，建议手动执行以下步骤以优化复杂查询性能：
  1. 查看当前执行计划：EXPLAIN <table_name>;
  2. 收集统计信息：ANALYZE <table_name>;
  3. 再次查看更新后的执行计划：EXPLAIN <table_name>;
• 切分大文件
  为了提高多节点并行扫描效率，建议将大文件切分为多个小文件。例如，在 Linux 系统中：

  wc -l <csv_file>
  split -l N <csv_file>
  

  注意：切分后的小文件需保证行的完整性，同一行记录不能跨文件存放。

• 查看 OSS 文件信息
  使用 get_oss_table_meta 函数获取 OSS Foreign Table 的文件信息：

  SELECT * FROM get_oss_table_meta('<OSS FOREIGN TABLE>');
  

2. SQL 查询优化技巧

ADB 提供了多种 SQL 查询优化方法，适用于复杂数据分析场景： - 去掉不必要的列
在查询中仅选择需要的列，避免全表扫描，减少 I/O 开销。 - 索引与扫描结合
当 SQL 包含多个查询条件时，优先选择高筛选条件进行索引过滤，其他条件可通过内部扫描实现。例如：

/*+ no_index_columns=[tab1.time] */
SELECT c1, c2 FROM tab1 WHERE c1=3 AND time>='2010-01-01 00:00:00';

说明：此方法适用于内核版本低于 3.14 的集群；3.14 及以上版本请使用 filter_not_pushdown_columns Hint。 - 不等于条件通过内部扫描
对于 <> 条件，建议通过内部扫描执行，避免索引扫描的低效性。例如：

/*+ no_index_columns=[tab1.c2] */
SELECT c1, c2 FROM tab1 WHERE c1=3 AND c2<>100;

• LIKE 条件优化
  对于中缀或后缀匹配（如 LIKE '%abc' 或 LIKE '%abc%'），建议通过内部扫描执行。

3. 命名规则与使用限制

为保障集群的稳定性和安全性，ADB 对数据库、表、列等对象的命名有严格限制： - 数据库名
- 长度不超过 64 个字符。 - 以小写字母开头，可包含字母、数字和下划线（_）。 - 禁止包含连续两个及以上的下划线，且不能命名为 analyticdb。 - 表名
- 长度为 1~127 个字符。 - 以字母或下划线开头，可包含字母、数字和下划线。 - 禁止包含引号、感叹号（!）和空格，且不能是 SQL 保留关键字。 - 列名
- 长度为 1~127 个字符。 - 命名规则与表名一致。 - 账号名
- 长度规则：
- 内核版本低于 3.1.9.4：长度为 2~16。
- 内核版本 3.1.9.4 及以上：长度为 2~64。
- 以小写字母开头，以小写字母或数字结尾，可包含小写字母、数字和下划线。

4. 高级 SQL 功能

ADB 支持多种高级 SQL 功能，满足复杂的数据分析需求： - 使用 HAVING 子句过滤分组聚合数据
示例：查询每个市场细分和国家代码组合的客户数量及总账户余额，仅显示总账户余额大于 5700000 的分组：

SELECT COUNT(*), mktsegment, nationkey,
       CAST(SUM(acctbal) AS BIGINT) AS totalbal
FROM customer
GROUP BY mktsegment, nationkey
HAVING SUM(acctbal) > 5700000
ORDER BY totalbal DESC;

• 字符串分割与数组展开
  使用 SPLIT 函数将字符串分割为数组，并通过 CROSS JOIN 和 UNNEST 将数组元素转换为单独的行。例如：

  SELECT userid, col
  FROM (
    SELECT userid, SPLIT(product, ',') AS numbers_array
    FROM test
  )
  CROSS JOIN UNNEST(numbers_array) AS temp_table(col);
  

• 关联子查询
  查询商品的最低折扣：

  SELECT id,  
    (SELECT MIN(discount)
     FROM item
     WHERE goods.id = goods_id)
  FROM goods;
  

  注意：关联列仅允许出现在子查询的 WHERE 子句中，且子查询不能返回多行结果。

5. 数据湖分析与外部数据源访问

ADB 支持高效访问和管理外部数据源，包括 MaxCompute 和 Hadoop 生态系统： - 创建 ODPS Foreign Table
1. 创建 ODPS FDW 插件： sql CREATE EXTENSION odps_fdw; 2. 创建 ODPS Server 和 User Mapping： ```sql CREATE SERVER odps_serv FOREIGN DATA WRAPPER odps_fdw OPTIONS (tunnel_endpoint '');

 CREATE USER MAPPING FOR username
 SERVER odps_serv
 OPTIONS (id '<odps access id>', key '<odps access key>');
 ```

1. 创建 ODPS Foreign Table：

   CREATE FOREIGN TABLE odps_table_name (
     column_name data_type [, ...]
   )
   SERVER odps_serv
   OPTIONS (project '<odps project>', table '<odps table>');
   

2. 访问 Hadoop 生态数据源
3. 创建扩展：

   CREATE EXTENSION pxf;
   

4. 创建外表：

   CREATE EXTERNAL TABLE <table_name>
   ( <column_name> <data_type> [, ...] | LIKE <other_table> )
   LOCATION('pxf://<path-to-data>?PROFILE[&<custom-option>=<value>[...]]&[SERVER=value]')
   FORMAT '[TEXT|CSV|CUSTOM]' (<formatting-properties>);
   

6. 排序与集合运算

• 使用 ORDER BY 排序
  示例：统计设备销售数量排名前 5 的城市，并按设备名称降序、销售量升序排序：

  SELECT os, device, city, COUNT(*) AS num 
  FROM requests 
  GROUP BY os, device, city 
  ORDER BY 2 DESC, 4 ASC 
  LIMIT 5;
  

• 集合运算符
  支持 UNION、INTERSECT 和 EXCEPT 操作，用于合并或比较查询结果。语法如下：

  query
  { UNION [ ALL ] | INTERSECT | EXCEPT }
  query
  

通过以上技巧，您可以显著提升 ADB 的使用效率和查询性能。