Impala SQL的迁移

基于 impala 3.2 版本

Impala使用标准SQL查询,但是由于数据类型、内置函数、供应商语言扩展以及Hadoop特定语法的变化,在将应用程序迁移到Impala
时还可能需要修改SQL源。即使SQL工作正常,您也可能进行进一步的细微修改以获得最佳性能。

从其他的数据库系统中迁移需要考虑如下几个方面:

1.DDL和DML语句的迁移

在将SQL代码从遗留的数据库系统适配到Impala时,希望在用于设置模式的DDL语句中发现许多不同之处。 SQL语句的物理文件布局
,tablespace,index等,在Impala中没有等价的子句。您可能会对模式进行大量重组,以考虑Impala分区方案和Hadoop文件格式。

若期望SQL查询具有更高程度的兼容性则需要通过适度的重写,以解决Impala中尚不支持的供应商扩展和功能,您可能需要在两个
系统上运行相同或几乎相同的查询文本。

因此,可以考虑将DDL分离到单独的Impala特定的安装脚本中。将重用和持续的优化工作集中在SQL查询的代码上

2.数据类型的适配

VARCHAR, VARCHAR2, and CHAR    --->String (长度需要移除掉)
NCHAR, NVARCHAR, or NCLOB      --->String (这些国家语言需要使用UTF-8)
DATETIME,TIME                  ---> DATE or TIMESTAMP
SYSDATE                        ---> now()

date_add/date_sub              ---> NOW() + INTERVAL 30 DAYS
YEAR                           ---> Int Type
DECIMAL and NUMBER            --->  Decimal
FLOAT, DOUBLE, and REAL         ---> FLOAT, DOUBLE(REAL)
BLOB, CLOB or TEXT             ---> 32K 的String替代
BOOL                            ---> BOOLEAN 
 BLOB, RAW BINARY, and VARBINARY  ---> impala 没有等价类型
空间数据类型                       ---> String 或者 UDF



1.删除所有UNSIGNED约束。所有的Impala数值类型都是有符号的。

2.由于Impala当前不支持复合类型或嵌套类型,因此其他数据库系统中的任何空间数据类型在Impala中都没有直接的等价物。
您可以用字符串格式表示空间值,并编写UDF来处理它们。在可行的情况下,将空间类型分开到单独的表中,这样Impala仍然
可以处非空间数据。

3.Impala 不支持 PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, UNSIGNED, or CHECK,在数据处理的过程如Hive
或者MapReduce中则可以考虑ETL的过程进行清洗

4.取出所有CREATE INDEX、DROP INDEX和ALTER INDEX语句,以及等效的ALTER TABLE语句。从CREATE TABLE和ALTER TABLE
语句中删除任何INDEX、KEY或PRIMARY KEY子句。Impala针对数据仓库样式查询的批量读取操作进行了优化,因此不支持其表的索引。

5.Impala只返回NULL,不抛出异常。
6.Impala不支持的数据类型,推荐用STRING+UDFs统一解决。
7.检测数据文件里是否有不支持或者无法转换的数据,按照如下运行,可以快速发现错误,终止Query

set abort_on_error=true;
select count(*) from (select * from t1);

3.SQL语句的适配

1. delete 语句的支持仅限于 kudu表
    Impala适用于数据仓库式操作,您可以进行大量移动和转换大量数据。不使用Kudu表时,使用INSERT OVERWRITE完全替换表
或分区的内容或使用INSERT ... SELECT将数据子集(除要删除的行外的所有内容)从一个表复制到另一个表。

2. update 语句的支持仅限于 kudu 表
3. upset  语句的支持仅限于 kudu表
  有些数据中同样的功能是merge语句

4.Impala不支持事务,因此没有COMMIT or ROLLBACK. 可以将impala的事务视为其他数据库中的 autocommit
5. 若database, table, column, or other name和Impala的保留关键字冲突 考虑使用票号(``)
6.Impala的子查询有一些限制
7.Impala支持 union 和union all操作 不支持INTERSECT
8.Impala要求在FROM子句中用作内联视图( inline views )的子查询使用查询别名。 和MySQL一样.
  select *  from (select a  from t) tt
9.当为查询中的表达式声明别名时,不能在同一SELECT列表中再次引用该别名。
  select a avg, a+20 b from t -- 正确
  select a avg, avg+20 b from t  --错误

  这种将字段别名用于后续的字段查询 在有些列式数据库是支持的如clickhouse和sybase IQ

10.impala 不支持 NATURAL JOIN 和USING语句
11.Impala支持有限的分区类型选择。
12. topN的语句在Impala通过 LIMIT实现,而不是 伪列 ROWNUM or ROW_NUM.

 

4.SQL结构的双检

一些受支持SQL构造的行为或默认值更趋向于方便而不是最佳性能。此外,有时机器生成的SQL(可能通过JDBC或ODBC应用程序发布)
可能效率低下或超出了Impala内部限制。在移植SQL代码时,检查并酌情更新以下内容:

1.不带STORED AS子句的CREATE TABLE语句以纯文本格式创建数据文件,这很方便数据交换,但对于大容量的高性能查询,这不是最佳选择。

2. 没有PARTITIONED BY的CREATE TABLE,默认将全部数据存入一个文件,可能会导致这个文件数据容量过大,超出上限。

3.INSERT ... VALUES语法适用于在与HDFS一起使用时设置具有几行的功能测试的玩具表,这会导致在HDFS中存在很多小文件,对于GB TB数据来说不利于性能的扩展

考虑修改数据加载过程,在Impala外部生成原始数据文件,然后设置Impala外部表或使用LOAD DATA语句立即在Impala表中使用这些数据文件,而无需转换或索引阶段。

INSERT对Kudu表非常有效,尽管速度不是特别快。

4.如果ETL没有做优化,可能会导致在HDFS上产生很多的小文件,最好用 INSERT...SELECT重新整理数据,创建新表。

5.复杂查询可能具有较高的代码生成时间。作为解决方法,如果由于此原因导致查询失败,请设置查询选项DISABLE_CODEGEN=true。

6.UNION ALL比UNION效率更高,建议优先使用。

5.SQL语句和语法的验证

移植的应用程序充分利用了Impala的并行性、性能相关SQL功能以及与Hadoop组件的集成:
1.为了获得最佳性能,我们建议您在所有表上运行COMPUTE STATS。
2.对于数据卷、表结构和查询特性,请使用最有效的文件格式。
3.对经常用于WHERE子句中筛选的列进行分区。
4.ETL进程应该生成相对较少的多兆字节数据文件,而不是大量的小文件。

For the optimal performance, we recommend that you run COMPUTE STATS on all tables.
Use the most efficient file format for your data volumes, table structure, and query characteristics.
Partition on columns that are often used for filtering in WHERE clauses.
Your ETL process should produce a relatively small number of multi-megabyte data files rather than a huge number of small files.

 

参考:

CDP 7:

https://docs.cloudera.com/runtime/7.2.2/impala-sql-reference/topics/impala-porting.html

CDH6:

https://docs.cloudera.com/documentation/enterprise/latest/topics/impala_porting.html

CDH5:

https://docs.cloudera.com/documentation/enterprise/5/latest/topics/impala_porting.html

http://impala.apache.org/docs/build3x/html/topics/impala_porting.html

 

已标记关键词 清除标记
$ impala-shell -i slaver2 -f realdata_raw_to_parq.hql Starting Impala Shell without Kerberos authentication Connected to slaver2:21000 Server version: impalad version 2.5.0-cdh5.7.2 RELEASE (build 1140f8289dc0d2b1517bcf70454bb4575eb8cc70) Query: invalidate metadata changqing.t_wtdata_realdata_raw Fetched 0 row(s) in 0.06s Query: insert overwrite table changqing.t_wtdata_realdata partition(acqdate) select *, to_date(DateAcqTime) from changqing.t_wtdata_realdata_raw WARNINGS: Memory limit exceeded Error converting column: 12 TO DOUBLE (Data is: null) Error converting column: 13 TO DOUBLE (Data is: null) Error converting column: 27 TO DOUBLE (Data is: null) Error converting column: 51 TO DOUBLE (Data is: null) Error converting column: 52 TO DOUBLE (Data is: null) Error converting column: 53 TO DOUBLE (Data is: null) Error converting column: 54 TO DOUBLE (Data is: null) Error converting column: 60 TO DOUBLE (Data is: null) Error converting column: 61 TO DOUBLE (Data is: null) Error converting column: 62 TO DOUBLE (Data is: null) Error converting column: 63 TO DOUBLE (Data is: null) Error converting column: 64 TO DOUBLE (Data is: null) Error converting column: 65 TO DOUBLE (Data is: null) Error converting column: 66 TO DOUBLE (Data is: null) Error converting column: 67 TO DOUBLE (Data is: null) Error converting column: 68 TO DOUBLE (Data is: null) Error converting column: 69 TO DOUBLE (Data is: null) Error converting column: 70 TO DOUBLE (Data is: null) Error converting column: 71 TO DOUBLE (Data is: null) Error converting column: 72 TO DOUBLE (Data is: null) Error converting column: 73 TO DOUBLE (Data is: null) Error converting column: 74 TO DOUBLE (Data is: null) Error converting column: 75 TO DOUBLE (Data is: null) Error converting column: 76 TO DOUBLE (Data is: null) Error converting column: 77 TO DOUBLE (Data is: null) Error converting column: 78 TO DOUBLE (Data is: null) Error converting column: 79 TO DOUBLE (Data is: null) Error converting column: 80 TO DOUBLE (Data is: null) file: hdfs://master.hadoop.com:8020/user/hive/warehouse/changqing.db/t_wtdata_realdata_raw/part-m-00003_copy_3 record: 10,2015-02-01 00:00:00.0,10,-7,-6,409,408,408,0,0,0,50.03,null,null,0.726,2.52,2.98,2.77,-1.44,303.6,0.09,89.0,6,32.6,27.0,27.8,18.4,null,23.4,20.9,30.2,32.6,27.7,5.6,151.0,315.5,-0.4,7.0,3.0,9.9,-9.0E-4,0.0051,0.0,-44.5,1,1,233712,100.0,100.0,100.0,3,null,null,null,null,-0.3,11.0,89.0,89.0,89.0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0.0 Error converting column: 12 TO DOUBLE (Data is: null) Error converting column: 13 TO DOUBLE (Data is: null) Error converting column: 27 TO DOUBLE (Data is: null) Error converting column: 51 TO DOUBLE (Data is: null) Error converting column: 52 TO DOUBLE (Data is: null) Error converting column: 53 TO DOUBLE (Data is: null) Error converting column: 54 TO DOUBLE (Data is: null) Error converting column: 60 TO DOUBLE (Data is: null) Error converting column: 61 TO DOUBLE (Data is: null) Error converting column: 62 TO DOUBLE (Data is: null) Error converting column: 63 TO DOUBLE (Data is: null) Error converting column: 64 TO DOUBLE (Data is: null) Error converting column: 65 TO DOUBLE (Data is: null) Error converting column: 66 TO DOUBLE (Data is: null) Error converting column: 67 TO DOUBLE (Data is: null) Error converting column: 68 TO DOUBLE (Data is: null) Error converting column: 69 TO DOUBLE (Data is: null) Error converting column: 70 TO DOUBLE (Data is: null) Error converting column: 71 TO DOUBLE (Data is: null) Error converting column: 72 TO DOUBLE (Data is: null) Error converting column: 73 TO DOUBLE (Data is: null) Error converting column: 74 TO DOUBLE (Data is: null) Error converting column: 75 TO DOUBLE (Data is: null) Error converting column: 76 TO DOUBLE (Data is: null) Error converting column: 77 TO DOUBLE (Data is: null) Error converting column: 78 TO DOUBLE (Data is: null) Error converting column: 79 TO DOUBLE (Data is: null) Error converting column: 80 TO DOUBLE (Data is: null) Could not execute command: insert overwrite table changqing.t_wtdata_realdata partition(acqdate) select *, to_date(DateAcqTime) from changqing.t_wtdata_realdata_raw
©️2020 CSDN 皮肤主题: 技术工厂 设计师:CSDN官方博客 返回首页