# 跨库操作dblink和postgres_fdw 插件介绍： 使用dblink和postgres_fdw可以实现跨库操作其他PostgreSQL库。 # 系统要求 PostgreSQL 9.5+ 与要连接的其他PostgreSQL网络连通 # dblink 1、新建dblink插件。 ```sql CREATE EXTENSION dblink; ``` 2、连接远程数据库 ```sql --SELECT dblink_connect('', ''); SELECT dblink_connect('mydb', 'dbname=postgres port=5432 host=localhost'); dblink_connect ---------------- OK (1 row) ``` 该函数有两个参数:connname和connstr，其中connname是可选参数。 connname：要用于这个连接的名字。如果被忽略，将打开一个未命名连接并且替换掉任何现有的未命名连接。 connstr：数据库连接串信息，格式为：hostaddr= port=端口号> dbname=数据库名> user=用户名> password=密码> 3、执行sql命令 ```sql --执行查询 SELECT * FROM dblink('mydb', 'select * from test') as test(id integer, info varchar(8)); id | info ----+------ 1 | a 1 | b (2 rows) --执行插入 SELECT dblink_exec('mydb', 'insert into test values (3,''c'');'); dblink_exec ------------- INSERT 0 1 (1 row) SELECT * FROM dblink('mydb', 'select * from test') as test(id integer, info varchar(8)); id | info ----+------ 1 | a 2 | b 3 | c (3 rows) ``` 4、查询连接状态 ```sql SELECT dblink_get_connections(); dblink_get_connections ------------------------ {mydb} (1 row) ``` 5、关闭远程连接 ```sql SELECT dblink_disconnect('mydb'); ``` 更多内容请参考[dblink](https://www.postgresql.org/docs/12/dblink.html) # postgres_fdw 1、新建postgres_fdw插件 ```sql CREATE EXTENSION postgres_fdw; ``` 2、然后使用CREATE SERVER创建一个外部服务器 ```sql CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'x.x.x.x', port '5432', dbname 'foreign_db'); ``` 3、使用CREATE USER MAPPING一个用户映射,每一个用户映射都代表你想允许一个数据库用户访问一个外部服务器。 ```sql --指定要映射到外部服务器的本地数据库用户名local_user CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password'); ``` 4、为每一个你想访问的远程表使用CREATE FOREIGN TABLE或者IMPORT FOREIGN SCHEMA创建一个外部表 ```sql CREATE FOREIGN TABLE foreign_table ( id integer, info varchar(8) ) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table'); ``` 5、查询 ```sql SELECT * FROM foreign_table; id | info ----+------ 1 | a 2 | b 3 | c (3 rows) ``` 更多内容请参考[postgres_fdw](https://www.postgresql.org/docs/12/postgres-fdw.html)

该报错表示用户执行的sql在单DN上所用空间超过了参数sql_use_spacelimit的限制。当前该参数的值可通过show sql_use_spacelimit查看。sql_use_spacelimit表示：限制单个SQL在单个DN上，触发落盘操作时，落盘文件的空间大小，管控的空间包括普通表、临时表以及中间结果集落盘占用的空间。解决方法：如果设置了sql_use_spacelimit，可适当调大该参数如果该参数很大或为-1（-1表示不限制），则需要优化业务sql，可先对原表进行analyze，并排查是否有数据倾斜或中间结果集过大的情况。

【摘要】 为了最大限度提升应用开发人员的代码质量、减少业务SQL的性能风险、降低运维调优工作量，需要针对上线的SQL语句进行验收审核，并输出上线前验收Checklist，协助完成数据库开发规范自检。 SQL语句上线验收操作指导一、摘要为了最大限度提升应用开发人员的代码质量、减少业务SQL的性能风险、降低运维调优工作量，需要针对上线的SQL语句进行验收审核，并输出上线前验收Checklist，协助完成数据库开发规范自检。二、DML语句验收CheckList应用开发人员自检工作主要分为两个阶段，包括开发阶段和验收阶段：开发阶段：开发人员严格按照设计规范进行代码开发，并通过验收checklist中的排查方法和标准对所负责模块的SQL进行自检。验收阶段：业务人员进行系统全流程点击，根据第四章【附件3】的方法抓取TOP SQL，按照checklist排查方法验证是否符合验收标准，并汇总输出验收表，详细验收checklist如下所示： 三、DML语句验收标准DML（Data Manipulation Language数据操作语言），用于对数据库表中的数据进行操作。如：插入、更新、查询、删除，此处的DML语句还包括视图定义、存储过程中的SQL语句。标准1：执行下推&没有stream分布式数据库架构下需最大限度的降低查询时节点之间的数据流动，以提升查询效率，因此SQL语句执行要实现stream算子为0。可通过第四章【附件1】方式查看SQL语句执行计划，从而判断执行计划是否下推，以及是否含有stream算子。（一）判断执行计划是否下推 数据库后台根据第四章【附件3】的方法统计TOP SQL，如果TOP SQL中bxt_count列均为0，表示优化后没有不下推的SQL，验收通过。详细说明：如果执行计划中有Data Node Scan节点，那么此执行计划为不可下推的执行计划；如果执行计划中有Streaming节点，那么计划是可以下推的。下图执行计划信息(红色方框部分)可看出此SQL语句不能下推，这种场景需要分析并消除不下推的因素，具体可查看客户端连接的coordinator实例的日志信息辅助定位分析，并进行优化整改。 （二）判断执行计划是否含有stream算子数据库后台根据第四章【附件3】的方法抓取TOP SQL，如果TOP SQL中stream_count列均为0，表示优化后SQL不含有stream算子，验收通过。详细说明：执行计划中含有Streaming(type: Gather)，如果Streaming(type: Gather)下面的计划信息中存在Streaming字符串信息，那么执行计划含有stream算子，否则不含stream算子。（1）如下是含有stream算子的计划(下面的红色方框部分含有Streaming字符串信息)，需要进行SQL改写消除Stream算子。（2）如下是不含stream算子的执行计划(下面的红色方框部分不含Streaming字符串信息)。标准2：没有关联子查询数据库后台根据第四章【附件3】的方法抓取TOP SQL，如果TOP SQL中subplan_count列均为0，表示优化后没有关联子查询，验收通过。详细说明：当SQL语句存在不能提升的关联子查询时，执行计划中会显示SubPlan关键字，如下图所示。对于这种场景需要将关联子查询提升为跟父表的关联，消除SubPlan。标准3：有效使用索引关于索引，经常遇到的问题是缺乏索引、索引过滤效果不佳，这两类问题场景可通过第四章【附件2】方式查看SQL语句执行信息进行识别。（一）缺乏索引扫描命中率小于10%的SQL需要添加索引。如下执行信息中，从红色椭圆框可以看到表boss_t_fb_datasourceinfo过滤条件province = '610000' AND type = 'SELECT' AND year = 2019过滤掉2342条记录，最终输出0条记录，这种就是典型的缺乏索引的场景。（二）索引过滤效果不佳如下执行信息中，从红色椭圆框可以看到表epay_t_voucherreceive_log 经过索引index_pki_epay_voucherreceive_log_vouno扫描之后，还需要经过条件vtcode = '5106' AND voucherstatus = 1过滤掉118682个元组，最终输出393条元组，这种就是典型的索引过滤效果不明显的场景，需要进行索引优化。（三）高效索引特征高效索引一般会直接通过Index Cond命中绝大部分有效输出，体现在执行信息上为没有“Rows Removed by Filter:”输出，如下图所示。或者“Rows Removed by Filter:”后面跟的数字远小于对应算子在A-rows列的数据，或者“Rows Removed by Filter:”后面跟的数字非常小（例如调优经验参考值，该数字小于100）。标准4：避免冗余ORDER BY语句冗余ORDER BY场景主要出现在含有string_agg函数的SQL语句中，如下图所示，括号内的order by动作需要提升在父查询中，否则子查询的排序结果不能传递给父查询，会导致string_agg函数的输出出现非预期结果。标准5：SELECT FOR UPDATE语句必须在事物块中使用for update语句功能是在当前事务中对指定行进行加锁，事务提交后释放。该语句必须在事务块或者存储过程中使用，且锁会持续到事务结束。如果在事务块或者存储过程外使用，SQL语句执行完成之后相关锁就会自动释放，无法实现预期的锁效果。标准6：不能对复制表进行并发更新操作分布式场景下业界通用准则是将字典表(又称维度表)建成复制表，使用复制表可减少参与计算的线程数和减少网络数据交互，以提升查询性能。从业务上角度分析，这类表的数据相对稳定，通常对这类数据进行只读操作，仅当基础信息发生变更时才会由业务维护人员对字典表进行修改。因此从数据特征上讲，复制表不会发生并发更新动作，如果存在并发更新场景，就需要考虑复制表的设计是否合适。标准7：递归调用语句必须存在递归终结条件建议谨慎使用递归语句(WITH RECURSIVE)，使用WITH RECURSIVE的时候一定要注意递归调用的终止条件，确保递归可终止，否则会进入死循环，导致内存耗尽或者下盘文件撑爆磁盘空间，最终导致集群不可用。如下语句中，如果存在满足多条记录的superguid和guid成环的场景(比如表gl_t_account_subject中满足条件code = '2011' AND acctsystypeguid = 'DCD3A09596DF4B339F3406107871A7B4' AND province = '610324' AND year = 2020的记录的superguid和guid相等)，就会导致递归调用陷入死循环，中间结果下盘导致磁盘空间被占满。WITH   RECURSIVE result AS(    SELECT        guid, code, name, superguid,   province, year    FROM gl_t_account_subject    WHERE code = '2011'    AND acctsystypeguid =   'DCD3A09596DF4B339F3406107871A7B4'    AND province = '610324' AND year = 2020    UNION ALL    SELECT        k.guid, k.code, k.name, k.superguid,   k.province, k.year    FROM gl_t_account_subject k    INNER JOIN result c    ON c.superguid = k.guid    WHERE k. acctsystypeguid = 'DCD3A09596DF4B339F3406107871A7B4'    AND k.province = '610324'    AND k.year = 2020)SELECT      guid, code, nameFROM   resultWHERE   province = '610324' AND year = 2020ORDER   BY code ;四、附件附件1：查看执行计划查看SQL执行计划时，仅需在SQL语句前面加上explain关键字，在数据库中执行就会输出SQL语句的执行计划(不会导致SQL语句的实际执行)。explainSELECT   *FROM epay_vw_pay_voucher_billWHERE billno =   '6100001022204000007'AND province = '610000' AND   year = 2019;附件2：查看执行信息添加explain关键字会显示SQL执行计划，但并不会实际执行sql语句，explain analyze会实际执行sql语句并返回执行信息。查看执行信息时，需要在SQL语句前面加上explain analyze关键字，在数据库执行就会输出SQL语句的实际执行信息，每一个步骤为一个数据库运算符。explain analyzeSELECT   *FROM epay_vw_pay_voucher_billWHERE billno =   '6100001022204000007'AND province = '610000' AND   year = 2019;附件3：统计TOP SQL为了保障系统稳定运行，SQL上线前都需要覆盖检查和优化，避免因不规范SQL导致系统运行卡顿或资源耗尽。因此，需要增强巡检和校验手段，识别出耗时、高频、后台临时线程较多等需优化的TOP SQL，并进行整改，测试后再上线使用，为测试充分性增加一道防护网。本小节内容指导应用开发人员进行TOP SQL统计收集。（一）开启SQL统计参数开启SQL统计功能，然后进行业务连跑，数据库后台会自动记录SQL执行信息，业务连跑结束之后，查询active SQL视图，获取SQL执行信息，查找耗时、高频、后台临时线程较多等需优化的TOP SQL进行重点优化分析。登陆任一数据节点，切换到omm用户，执行如下命令开启active SQL统计功能。gs_guc reload -Z datanode -Z coordinator -N all -I all -c "enable_resource_track = on"gs_guc reload -Z datanode -Z coordinator -N all -I all -c "enable_resource_record = on"gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_level = query"gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_cost = 100"gs_guc reload -Z datanode -Z coordinator -N all -I all -c "resource_track_duration = 0"（二）TOP SQL收集1、准备工作（1）更新统计信息在数据库中，统计信息是规划器生成计划的源数据。没有收集统计信息或者统计信息陈旧往往会造成执行计划严重劣化，从而导致性能问题。检测前需要进行全库统计信息收集。通过执行ANALYZE语句可收集与数据库中表内容相关的统计信息，统计结果存储在系统表PG_STATISTIC中，查询优化器会使用这些统计数据，以生成最有效的执行计划，以对postgres库执行analyze操作为例执行如下命令，其余数据库仅需修改-d后面的库名即可。gsql -d postgres -p 25308 -c ‘analyze’ （2）统计表初始化如果在检测前active SQL功能已经打开，需要执行以下动作清理历史SQL统计信息。gs_ssh -c “gsql -d postgres -p 25308 -c ‘delete from gs_wlm_session_info’”gsql -d postgres -p 25308 -c ‘vacuum full gs_wlm_session_info’2、获取TOP SQL列表按照本章第1小节完成操作前准备，执行如下函数进行SQL检测，统计出TOP SQL。（1）脚本准备a.筛选subplan登陆postgres数据库创建如下存储过程，统计执行计划中的subplan数量。CREATE OR REPLACE FUNCTION public.subplan_count(text) RETURNS integer LANGUAGE sql IMMUTABLE STRICT NOT FENCEDAS $function$    select ((length($1) -   length(replace($1, 'SubPlan', '')) )::int / length('SubPlan'))::int$function$;b.筛选Stream算子登陆postgres数据库创建如下存储过程，统计执行计划中的Stream算子数量。CREATE OR REPLACE FUNCTION public.stream_count(text) RETURNS integer LANGUAGE sql IMMUTABLE STRICT NOT FENCEDAS $function$    select ((length($1) -   length(replace(replace($1, 'Streaming(type: B', ''), 'Streaming(type: R',   ''))) / length('Streaming(type: B'))::int$function$;   C．筛选不下推SQL   登录postgres数据库创建如下存储过程，如果存储过程调用结果大于0，则该SQL为不下推SQL。CREATE OR REPLACE FUNCTION public.bxt_count(text) RETURNS integer LANGUAGE sql IMMUTABLE STRICT NOT FENCEDAS $function$    select ((length($1) -   length(replace($1, '_REMOTE_TABLE_QUERY_', '')) )::int /   length('_REMOTE_TABLE_QUERY_'))::int$function$; （二）统计TOP SQL登陆postgres数据库，通过sql语句统计Topsql列表。selectsubstr(query, 1, 60) as   sub_query,                                --截取sql语句的1-60字段进行分组统计       dbname,                                             --数据库名       count(1) as count,                                      --sql调用频次       round(avg(duration), 2) as   avg_duration,                    --sql平均执行时间                       public.stream_count(query_plan) as   stream_count,            --统计执行计划中stream算子数          public.subplan_count(query_plan) as   subplan_count,          --统计执行计划中subplan个数  public. bxt_count (query_plan) as bxt_count,                 --统计执行计划中不下推次数        max(queryid)   as query_id                                       --根据queryid查询具体SQL  from pgxc_wlm_session_info   where dbname in ('chw_pems')                                 --数据库名   and start_time > '2020-03-15   19:00:00'                         --开始时间   and finish_time < '2020-03-15   20:00:00'                        --结束时间group by 1,2,5,6,7having(stream_count > 0   or subplan_count > 0 or bxt_count>0)               order by stream_count desc;SQL查询结果如下：上述步骤截取sql语句的前60个字符，可根据queryid（图中max列信息） 查询完整的sql语句。--使用上例sql查出来TOP SQL的queryid，查询完整的sql语句select query from  pgxc_wlm_session_info where   queryid='xxxxx';华为云社区论坛链接：https://bbs.huaweicloud.com/forum/forum-598-1.html

背景及现象描述(Background and Symptom)*1.      环境信息：GaussDB A 8.0.0版本12节点集群2.      问题现象：客户反映业务执行慢，原本几分钟的业务一个小时都跑不完，造成大量业务累计。 3.       分析过程1.       初步分析为客户连接数过高导致IO高，限制连接数后IO稍有回落，后续IO又升高至95%以上且限制连接数后严重影响用户使用2.       连到数据库，查用线程的id，查pgxc_thread_wait_status，可以找到对应语句的query_id可以看到该sql处理flush data状态3.       检查下盘文件，发现每个数据目录下均有不等的下盘文件，且个别sql的下盘文件数达到3000+下盘文件中间部分的id为query_id，根据query_id找到对应的sql与业务侧同事确认该sql不合理，需要整改4.       确认work_mem设置大小当前集群work_mem设置为512MB，设置的过小导致下盘情况容易产生，目前调整为3GB原因分析(Cause Analysis)*当work_mem设置较小场景时，会产生大量的下盘，时时在数据盘上读写文件，导致数据盘读写IO繁忙，因此影响其他业务运行。解决办法(Solution)*（1）       调整work_mem,使其满足大部分业务的使用场景（2）       整改不合理业务，使客户系统运行更顺畅。

【摘要】 配置SQL ON OBS桶策略，实现DWS访问分离详情请点击博文链接：https://bbs.huaweicloud.com/blogs/246157

参考执行流程

全文检索（Text search）顾名思义，就是在给定的文档中查找指定模式（pattern）的过程。GaussDB(DWS)支持对表格中文本类型的字段及字段的组合做全文检索，找出能匹配给定模式的文本，并以用户期望的方式将匹配结果呈现出来。本文结合笔者的经验和思考，对GaussDB(DWS)的全文检索功能作简要介绍，希望能对读者有所帮助。 1.   预处理         在指定的文档中查找一个模式有很多种办法，例如可以用grep命令搜索一个正则表达式。理论上，对数据库中的文本字段也可以用类似grep的方式来检索模式，GaussDB(DWS)中就可以通过关键字“LIKE”或操作符“~”来匹配字符串。但这样做有很多问题。首先对每段文本都要扫描，效率比较低，难以衡量“匹配度”或“相关度”。而且只能机械地匹配字符串，缺少对语法语义的分析能力，例如对英语中的名词复数，动词的时态变换等难以自动地识别和匹配，对于由自然语言构成的文本无法获得令人满意的检索结果。         GaussDB(DWS)采用类似搜索引擎的方式来进行全文检索。首先对给定的文本和模式做预处理，包括从一段文本中提取出单词或词组，去掉对检索无用的停用词（stop word），对变形后的单词做标准化等等，使之变为适合检索的形式再作匹配。         GaussDB(DWS)中，原始的文档和搜索条件都用文本（text）表示，或者说，用字符串表示。经过预处理后的文档变为tsvector类型，通过函数to_tsvector来实现这一转换。例如，postgres=# select to_tsvector('a fat cat ate fat rats');            to_tsvector           ----------------------------------- 'ate':4 'cat':3 'fat':2,5 'rat':6(1 row)         观察上面输出的tsvector类型，可以看到to_tsvector的效果：首先各个单词被摘取出来，其位置用整数标识出来，例如“fat”位于原始句子中的第2和第5个词的位置。此外，“a”这个词太常见了，几乎每个文档里都会出现，对于检索到有用的信息几乎没有帮助。套用香农理论，一个词出现的概率越大，其包含的信息量越小。像“a”，“the”这种单词几乎不携带任何信息，所以被当做停用词（stop word）去掉了。注意这并没有影响其他词的位置编号，“fat”的位置仍然是2和5，而不是1和4。另外，复数形式的“rats”被换成了单数形式“rat”。这个操作被称为标准化（Normalize），主要是针对西文中单词在不同语境中会发生的变形，去掉后缀保留词根的一种操作。其意义在于简化自然语言的检索，例如检索“rat”时可以将包含“rat”和“rats”的文档都检索出来。被标准化后得到的单词称为词位（lexeme），比如“rat”。而原始的单词被称为语言符号（token）。         将一个文档转换成tsvector形式有很多好处。例如，可以方便地创建索引，提高检索的速度和效率，当文档数量巨大时，通过索引来检索关键字比grep这种全文扫描匹配要快得多。再比如，可以对不同关键字按重要程度分配不同的权重，方便对检索结果进行排序，找出相关度最高的文档等等。         经过预处理后的检索条件被转换成tsquery类型，可通过to_tsquery函数实现。例如，postgres=# select to_tsquery('a & cats & rat');  to_tsquery  --------------- 'cat' & 'rat'(1 row)         从上面的例子可以看到：跟to_tsvector类似，to_tsquery也会对输入文本做去掉停用词、标准化等操作，例如去掉了“a”，把“cats”变成“cat”等。输入的检索条件本身必须用与（&）、或（|）、非（！）操作符连接，例如下面的语句会报错postgres=# select to_tsquery('cats rat');ERROR:  syntax error in tsquery: "cats rat"CONTEXT:  referenced column: to_tsquery         但plainto_tsquery没有这个限制。plainto_tsquery会把输入的单词变成“与”条件：postgres=# select plainto_tsquery('cats rat'); plainto_tsquery----------------- 'cat' & 'rat'(1 row)postgres=# select plainto_tsquery('cats,rat'); plainto_tsquery----------------- 'cat' & 'rat'(1 row)         除了用函数之外，还可以用强制类型转换的方式将一个字符串转换成tsvector或tsquery类型，例如postgres=# select 'fat cats sat on a mat and ate a fat rat'::tsvector;                      tsvector                      ----------------------------------------------------- 'a' 'and' 'ate' 'cats' 'fat' 'mat' 'on' 'rat' 'sat'(1 row)postgres=# select 'a & fat & rats'::tsquery;       tsquery       ---------------------- 'a' & 'fat' & 'rats'(1 row)         跟函数的区别是强制类型转换不会去掉停用词，也不会作标准化，且对于tsvector类型不会记录词的位置。 2.   模式匹配         把输入文档和检索条件转换成tsvector和tsquery之后，就可以进行模式匹配了。GaussDB(DWS)中使用“@@”操作符来进行模式匹配，成功返回True，失败返回false。         例如创建如下表格，postgres=# create table post(postgres(# id bigint,postgres(# author name,postgres(# title text,postgres(# body text);CREATE TABLE-- insert some tuples         然后想检索body中含有“physics”或“math”的帖子标题，可以用如下的语句来查询：postgres=# select title from post where to_tsvector(body) @@ to_tsquery('physics | math');            title           ----------------------------- The most popular math books         也可以将多个字段组合起来查询：postgres=# select title from post where to_tsvector(title || ' ' || body) @@ to_tsquery('physics | math');            title           ----------------------------- The most popular math books(1 row)          注意不同的查询方式可能产生不同的结果。例如下面的匹配不成功，因为::tsquery没对检索条件做标准化，前面的tsvector里找不到“cats”这个词：postgres=# select to_tsvector('a fat cat ate fat rats') @@ 'cats & rat'::tsquery; ?column?---------- f(1 row)         而同样的文档和检索条件，下面的匹配能成功，因为to_tsquery会把“cats”变成“cat”：postgres=# select to_tsvector('a fat cat ate fat rats') @@ to_tsquery('cats & rat'); ?column?---------- t(1 row)          类似地，下面的匹配不成功，因为to_tsvector会把停用词a去掉：postgres=# select to_tsvector('a fat cat ate fat rats') @@ 'cat & rat & a'::tsquery; ?column?---------- f(1 row)         而下面的能成功，因为::tsvector保留了所有词：postgres=# select 'a fat cat ate fat rats'::tsvector @@ 'cat & rat & a'::tsquery; ?column?---------- f(1 row)         所以应根据需要选择合适的检索方式。         此外，@@操作符可以对输入的text做隐式类型转换，例如，postgres=# select title from post where body @@ 'physics | math'; title-------(0 rows)         准确来讲，text@@text相当于to_tsvector(text) @@ plainto_tsquery(text)，因此上面的匹配不成功，因为plainto_tsquery会把或条件'physics | math'变成与条件'physic' & 'math'。使用时要格外小心。 3.   创建和使用索引         前文提到，逐个扫描表中的文本字段缓慢低效，而索引查找能够提高检索的速度和效率。GaussDB(DWS)支持用通用倒排索引GIN（Generalized Inverted Index）进行全文检索。GIN是搜索引擎中常用的一种索引，其主要原理是通过关键字反过来查找所在的文档，从而提高查询效率。可通过以下语句在text类型的字段上创建GIN索引：postgres=# create index post_body_idx_1 on post using gin(to_tsvector('english', body));CREATE INDEX         注意这里必须使用to_tsvector函数生成tsvector，不能使用强制或隐式类型转换。而且这里用到的to_tsvector函数比前一节多了一个参数’english’，这个参数是用来指定文本搜索配置（Text search Configuration）的。关于文本搜索配置将在下一节介绍。不同的配置计算出来的tsvector不同，生成的索引自然也不同，所以这里必须明确指定，而且在查询的时候只有配置和字段都与索引定义一致才能通过索引查找。例如下面的查询中，前一个可以通过post_body_idx_1来检索，后一个找不到对应的索引，只能通过全表扫描检索。postgres=# explain select title from post where to_tsvector('english', body) @@ to_tsquery('physics | math');                                             QUERY PLAN                                             -----------------------------------------------------------------------------------------------------  id |            operation            | E-rows | E-width | E-costs ----+---------------------------------+--------+---------+---------   1 | ->  Streaming (type: GATHER)    |      1 |      32 | 42.02     2 |    ->  Bitmap Heap Scan on post |      1 |      32 | 16.02     3 |       ->  Bitmap Index Scan     |      1 |       0 | 12.00  postgres=# explain select title from post where to_tsvector('french', body) @@ to_tsquery('physics | math');                                          QUERY PLAN                                         ----------------------------------------------------------------------------------------------  id |          operation           | E-rows | E-width |     E-costs      ----+------------------------------+--------+---------+------------------   1 | ->  Streaming (type: GATHER) |      1 |      32 | 1000000002360.50   2 |    ->  Seq Scan on post      |      1 |      32 | 1000000002334.50 4.   全文检索配置（Text search Configuration）         这一节谈谈GaussDB(DWS)如何对文档做预处理，或者说，to_tsvector是如何工作的。         文档预处理大体上分如下三步进行：第一步，将文本中的单词或词组一个一个提取出来。这项工作由解析器（Parser）或称分词（Segmentation）器来进行。完成后文档变成一系列token。第二步，对上一步得到的token做标准化，包括依据指定的规则去掉前后缀，转换同义词，去掉停用词等等，从而得到一个个词位（lexeme）。这一步操作依据词典（Dictionary）来进行，也就是说，词典定义了标准化的规则。最后，记录各个词位的位置（和权重），从而得到tsvector。          从上面的描述可以看出，如果给定了解析器和词典，那么文档预处理的规则也就确定了。在GaussDB(DWS)中，这一整套文档预处理的规则称为全文检索配置（Text search Configuration）。全文检索配置决定了匹配的结果和质量。         如下图所示，一个全文检索配置由一个解析器和一组词典组成。输入文档首先被解析器分解成token，然后对每个token逐个词典查找，如果在某个词典中找到这个token，就按照该词典的规则对其做Normalize。有的词典做完Normalize后会将该token标记为“已处理”，这样后面的字典就不会再处理了。有的词典做完Normalize后将其输出为新的token交给后面的词典处理，这样的词典称为“过滤型”词典。  图1 文档预处理过程         配置使用的解析器在创建配置的时候指定，且不可修改，例如，postgres=# create text search configuration mytsconf (parser = default);CREATE TEXT SEARCH CONFIGURATION         GaussDB(DWS)内置了4种解析器，目前不支持自定义解析器。postgres=# select prsname from pg_ts_parser; prsname ---------- default ngram pound zhparser(4 rows)         词典则通过ALTER TEXT SEARCH CONFIGURATION命令来指定，例如postgres=# alter text search configuration mytsconf add mapping for asciiword with english_stem,simple;ALTER TEXT SEARCH CONFIGURATION指定了mytsconf使用english_stem和simple这两种词典来对“asciiword”类型的token做标准化。         上面语句中的“asciiword”是一种token类型。解析器会对分解出的token做分类，不同的解析器分类方式不同，可通过ts_token_type函数查看。例如，‘default’解析器将token分为如下23种类型：postgres=# select * from ts_token_type('default'); tokid |      alias      |               description                -------+-----------------+------------------------------------------     1 | asciiword       | Word, all ASCII     2 | word            | Word, all letters     3 | numword         | Word, letters and digits     4 | email           | Email address     5 | url             | URL     6 | host            | Host     7 | sfloat          | Scientific notation     8 | version         | Version number     9 | hword_numpart   | Hyphenated word part, letters and digits    10 | hword_part      | Hyphenated word part, all letters    11 | hword_asciipart | Hyphenated word part, all ASCII    12 | blank           | Space symbols    13 | tag             | XML tag    14 | protocol        | Protocol head    15 | numhword        | Hyphenated word, letters and digits    16 | asciihword      | Hyphenated word, all ASCII    17 | hword           | Hyphenated word, all letters    18 | url_path        | URL path    19 | file            | File or path name    20 | float           | Decimal notation    21 | int             | Signed integer    22 | uint            | Unsigned integer    23 | entity          | XML entity(23 rows)          当前数据库中已有的词典可以通过系统表pg_ts_dict查询。         如果指定了配置，系统会按照指定的配置对文档作预处理，如上一节创建GIN索引的命令。如果没指定配置，to_tsvector使用default_text_search_config变量指定的默认配置。postgres=# show default_text_search_config; -- 查看当前默认配置 default_text_search_config---------------------------- pg_catalog.english(1 row)postgres=# set default_text_search_config = mytsconf;  -- 设置默认配置SETpostgres=# show default_text_search_config; default_text_search_config---------------------------- public.mytsconf(1 row)postgres=# reset default_text_search_config;  -- 恢复默认配置RESETpostgres=# show default_text_search_config; default_text_search_config---------------------------- pg_catalog.english(1 row)          注意default_text_search_config是一个session级的变量，只在当前会话中有效。如果想让默认配置持久生效，可以修改postgresql.conf配置文件中的同名变量，如下图所示。修改后需要重启进程。总结         GaussDB(DWS)的全文检索模块提供了强大的文档搜索功能。相比于用“LIKE”关键字，或 “~”操作符的模式匹配，全文检索提供了较丰富的语义语法支持，能对自然语言文本做更加智能化的处理。配合恰当的索引，能够实现对文档的高效检索。         本文简要介绍了GaussDB(DWS)全文检索的原理和使用方法，关于解析器和词典的更详细的介绍，请看另一篇文章《GaussDB(DWS)全文检索之解析器和词典》（待完成）。

【功能模块】【操作步骤&问题现象】问题：1、在dayu中能否通过一些临时的sql去查询表数据？2、如何对dwi表新增数据【截图信息】【日志信息】（可选，上传日志内容或者附件）

可信智能计算服务TICS产品优势有：多域协同支持在分布式的、信任边界缺失的多个参与方之间建立互信联盟；实现跨组织、跨行业的多方数据融合分析和多方联合学习建模。灵活多态支持对接主流数据源（如MRS, RDS等）的联合数据分析；支持对接多种深度学习框架(TICS，TensorFlow)的联邦计算；支持控制流和数据流的分离，用户无需关心计算任务拆解和组合过程，采用有向无环图DAG实现多个参与方数据流的自动化编排和融合计算。自主高效数据使用全流程可视化展示，为数据参与方提供可感知、可监测的数据使用过程；支持数据参与方、计算方的多种部署模式，包括云上（同Region、跨Region）、边缘节点、HCS的部署模式；采用容器化资源/部署管理，支持调度方、数据参与方、计算方的弹性扩缩容。安全隐私支持用户自定义隐私策略，实现敏感数据的识别、脱敏、水印保护，最大程度的保障隐私数据安全；结合可信执行环境ArmTrustZone实现多方协同过程中隐私信息交互（SQL JOIN数据碰撞、联邦机器学习模型参数）的加密保护；支持安全多方计算，如基于隐私集合求交PSI（Private Set Intersection）技术的多方样本对齐, 基于差分隐私、加法同态、秘密共享等技术的训练模型保护；可插件化的对接区块链存储，实现多方数据的流动轨迹、使用过程的全程可追溯、可审计。

扩容问题分类扩容通常是在“初始化服务和实例”和数据重分布两个步骤报错，可通过查看后台日志排查分析。   日志分析步骤1、  如果是在“初始化服务和实例”步骤报错，则使用omm用户登录报错的新扩容节点，查看日志/var/log/Bigdata/mpp/scriptlog/postinstall.log，如果日志文件中没有内容“PreInstall cmd :execPython_preinstallMPP”，则表示扩容流程在FIM初始化阶段报错，排查FIM日志分析；2、  如果有则继续搜索日志中是否有错误“ PreInstall failed ”，如果能搜到说明配置新节点环境失败，根据日志上下文处理失败问题修复环境；3、  如果是扩容加节点失败，则使用omm用户登录第一个老节点，进入/var/log/Bigdata/mpp/scriptlog/目录下，查看gs_postinstall_*.log扩容日志文件，根据日志内容判断扩容失败原因。4、  如果重分布步骤失败，可登陆第一个老节点查看/var/log/Bigdata/mpp/scriptlog/目录下gs_postinstall_*.log扩容日志文件；如果日志显示是gs_redis报错，则登录集群第一个CN节点查看$GAUSSLOG/bin/gs_redis/目录下的gs_redis日志文件，搜索“failed”关键字获取具体的失败原因。 常见扩容问题1、  扩容（添加新节点）过程中因为断电、机器reboot、网络闪断导致扩容失败。· 修复硬件故障，确保GaussDB(DWS)集群所有机器网络连接正常；· 确保集群所有机器上没有扩容残留进程(gs_expand, gs_dump, gs_dumpall, gsql，gs_redis)。su - ommps ux |grep gs_expand |grep -v grep |awk '{print $2}' |xargs kill -9ps ux |grep gs_dump |grep -v grep |awk '{print $2}' |xargs kill -9ps ux |grep gsql |grep -v grep |awk '{print $2}' |xargs kill -9ps ux |grep gs_redis |grep -v grep |awk '{print $2}' |xargs kill -9· 在FI Manager页面上，点击扩容“重试”按钮重入扩容。2、扩容（添加新节点）过程中启动新集群失败而导致扩容失败。1、   以omm用户登录部署GaussDB(DWS)集群的任意一个节点，查看未启动的实例。source /opt/huawei/Bigdata/mppdb/.mppdbgs_profilecm_ctl query -Cvd2、  检查哪个实例状态不正常，是否由于实例端口号冲突导致启动失败。source /opt/huawei/Bigdata/mppdb/.mppdbgs_profilecd $GAUSSLOG/pg_log/异常实例日志目录grep -nr ‘Is another postmaster’ postgresql-*.log3、  扩容到元数据导入阶段，元数据导入失败，或者元数据导入耗时超过1小时未完成。·           查看CMS节点扩容OM日志，执行到Restoring new nodes这一步出现SQL失败，说明导入元数据失败。登陆任一新节点查看具体的sql失败信息：su - ommsource /opt/huawei/Bigdata/mppdb/.mppdbgs_profilecd $GAUSSLOG/om/ls -trl gs_local*.logtail -f gs_local*.log根据日志找到restore失败的SQL语句。分析失败原因，如果确认是SQL语法不支持，导入到新集群失败，需要先回退到老集群，然后删除或者修改不支持的SQL语法。记录不支持的SQL语句；在FI Manager界面上点击扩容“回退”按钮，回退到老集群；使用gsql客户端连接CN节点，删除或者修改不支持SQL语句。4、  扩容到元数据导出阶段，由于事务锁资源不足，导致元数据导出失败。·           以omm用户登录CMS主服务器节点。执行如下命令：source /opt/huawei/Bigdata/mppdb/.mppdbgs_profilegrep -nr "You might need to increase max_locks_per_transaction" $GAUSSLOG/bin/gs_dump/gs_dump -*-current.log若上述命令执行有结果输出，则说明GaussDB(DWS)集群中因事务锁不足导致扩容dump失败。·           获取GaussDB(DWS)的行存表、列存表、分区表、外表、索引、视图等数据库对象的数量。max_locks_per_transaction * (max_connections + max_prepared_transactions) 必须大于对象个数。在GaussDB(DWS)集群任意一个节点，参照如下命令，修改GUC参数配置：source /opt/huawei/Bigdata/mppdb/.mppdbgs_profilegs_guc set -Z coordinator -N all -I all -c 'max_connections = 1024' -c 'max_prepared_transactions = 1000' -c 'max_locks_per_transaction = 1024'gs_guc set -Z datanode -N all -I all -c 'max_connections = 3000'  -c 'max_prepared_transactions = 1000' -c 'max_locks_per_transaction = 1024'cm_ctl stop && cm_ctl start如果元数据导出阶段耗时超过1小时未完成，在GaussDB(DWS)集群任意一个节点，通过如下方法查看dump进度：source /opt/huawei/Bigdata/mppdb/.mppdbgs_profilecd $GAUSSLOG/bin/gs_dumpall/grep -nr "objects have been dumped" gs_dumpall-*-current.log5、  后台下发执行扩容重分布命令失败·           如果失败日志中不存在’If you want to check the progress of redistribution, please check the log file on’内容,说明还未开始重分布，根据失败报错提示查询原因并处理,如果日志存在If you want to check the progress of redistribution, please check the log file on’内容，查看日志提示的对应节点上重分布日志gs_redis-XXX.log。·           查看gs_redis-XXX.log日志里面的错误信息，1）如果错误信息提示：memory is temporarily unavailable，修改重分布并发度，重试下发扩容重分布命令。2)  如果提示连接DN失败，检查对应DN的状态并修复，然后重试下发扩容重分布。3）如果提示出现磁盘损坏，修复磁盘，如果无法修复可以进行节点替换。本文转载于华为云数仓GaussDB(DWS)公众号

一、Web漏洞暴力破解、文件上传、文件读取下载、SSRF、代码执行、命令执行 逻辑漏洞（数据遍历、越权、认证绕过、金额篡改、竞争条件） 应用层拒绝服务漏洞 人为漏洞：弱口令漏洞等级如下：严重直接获取重要服务器（客户端）权限的漏洞。包括但不限于远程任意命令执行、上传 webshell、可利用远程缓冲区溢出、可利用的 ActiveX 堆栈溢出、可利用浏览器 use after free 漏洞、可利用远程内核代码执行漏洞以及其它因逻辑问题导致的可利用的远程代码执行漏洞； 直接导致严重的信息泄漏漏洞。包括但不限于重要系统中能获取大量信息的SQL注入漏洞； 能直接获取目标单位核心机密的漏洞；高危直接获取普通系统权限的漏洞。包括但不限于远程命令执行、代码执行、上传webshell、缓冲区溢出等； 严重的逻辑设计缺陷和流程缺陷。包括但不限于任意账号密码修改、重要业务配置修改、泄露； 可直接批量盗取用户身份权限的漏洞。包括但不限于普通系统的SQL注入、用户订单遍历； 严重的权限绕过类漏洞。包括但不限于绕过认证直接访问管理后台、cookie欺骗。 运维相关的未授权访问漏洞。包括但不限于后台管理员弱口令、服务未授权访问。中危需要在一定条件限制下，能获取服务器权限、网站权限与核心数据库数据的操作。包括但不限于交互性代码执行、一定条件下的注入、特定系统版本下的getshell等； 任意文件操作漏洞。包括但不限于任意文件写、删除、下载，敏感文件读取等操作； 水平权限绕过。包括但不限于绕过限制修改用户资料、执行用户操作。低危能够获取一些数据，但不属于核心数据的操作； 在条件严苛的环境下能够获取核心数据或者控制核心业务的操作； 需要用户交互才可以触发的漏洞。包括但不限于XSS漏洞、CSRF漏洞、点击劫持；二、辅助工具知识基础： 《Web安全测试》 《HTTP权威指南》 《TCP/IP详解卷1:协议》HTTP(s)监听工具： Fiddle2/4 Burpsuite1/2 Charlies …… Postman其他辅助： 网络层监听：Wireshark webservice调试：SoapUI Pro API调试：ReadyAPI SQL注入：Sqlmap三、内测漏洞安全测试的指导原则： 所有的输入和输出都是危险的。内测重点漏洞类型： （1）文件上传（webshell 可请求类型html htm shtml txt） （2）越权（无、平行越权、垂直越权） （3）公民敏感信息泄露（姓名、身份证、IP、电话） （4）目录穿越任意文件下载 （5）存储型XSS跨站脚本 （6）防护措施失效（无防护、验证码，IP验证、登录验证码等） （7）Id可遍历可预测，导致信息泄露，文件泄露转载链接：https://www.zhihu.com/question/22226126/answer/883131662

相比此前其它人提取，该效率更高：create or replace view public.pg_wait_locks as  select distinct waits.query as waiting_query, waits.pid as w_pid, waits.usename as w_user, lck.coorname as lock_cn_node, lck.query as locking_query, lck.pid as l_pid, lck.usename as l_user, lckw.relation::regclass::varchar from pgxc_stat_activity waits join pg_locks lckw on waits.pid=lckw.pid and not lckw.granted join pg_locks lckl /*multi-row*/ on lckw.relation=lckl.relation and lckl.granted  join pgxc_stat_activity lck on lck.pid=lckl.pid where waits.waiting; select * from pg_wait_locks;欢迎大家来PK交流。。。

通过数据接入服务（Data Ingestion Service，简称DIS），可以将实时数据从DIS导入到GaussDB(DWS) 集群的数据库中。在这种场景下，DIS通道里的流式数据存储在DIS中，并周期性导入GaussDB(DWS) 中。导入GaussDB(DWS) 前数据临时存储在OBS，待转储GaussDB(DWS) 完成后删除OBS上的临时存储数据。从DIS导入数据到GaussDB(DWS) 的流程如下：1. 创建GaussDB(DWS) 集群、数据库和数据表2. 开通DIS通道，接入实时数据3. 在GaussDB(DWS) 数据库中查看从DIS导入的数据创建GaussDB(DWS) 集群、数据库和数据表1. 创建GaussDB(DWS) 集群。如果您已经有GaussDB(DWS) 集群了，也可以跳过这一步。例如，创建一个名为"dws-demo"的GaussDB(DWS) 集群。2. 使用SQL客户端连接GaussDB(DWS) 集群，选择其中一种方式连接集群。3. 在SQL客户端中，执行SQL语句，创建数据库、数据表和数据库模式（即schema）。参考如下参数完成数据库对象的创建： 数据库用户：用户名为“joe”， 密码为“Bigdata@123”。 数据库：“db_tpcds”。 数据库模式：“myschema”。如果您选择不创建数据库模式，默认情况下，新的数据库对象是创建在“public”模式下的。数据表：“mytable”。创建数据表时，请根据实际的源数据设计表结构。表的字段及其字段类型要和源数据一一对应。开通DIS通道，接入实时数据1. 登录DIS管理控制台，开通DIS通道。开通DIS通道的详细步骤，请参见开通DIS通道。从DIS导入数据到GaussDB(DWS) 的场景，对DIS通道的要求如下： 区域：必须选择与GaussDB(DWS) 集群相同的区域。目前区域仅支持“华北-北京四”。 源数据类型：只支持“CSV”。在DIS管理控制台，为刚购买的接入通道添加转储任务，“转储服务类型”选择“GaussDB(DWS) ”，将通道数据转储至GaussDB(DWS) 服务。添加转储任务时，GaussDB(DWS) 相关参数可按照创建GaussDB(DWS) 集群、数据库和数据表步骤中的情况进行填写。说明如下：转储服务类型：选择“GaussDB(DWS) ”。通道里的流式数据存储在DIS中，并周期性导入GaussDB(DWS) 中。导入GaussDB(DWS) 前数据临时存储在OBS，待转储GaussDB(DWS) 完成后删除OBS上的临时存储数据。 GaussDB(DWS) 集群：存储该通道数据的GaussDB(DWS) 集群名称。例如：“dws-demo ”。 GaussDB(DWS) 数据库：该通道数据的GaussDB(DWS) 数据库名称。例如：“db_tpcds”。 数据库模式：存储该通道数据的GaussDB(DWS) 数据库模式（即schema）。例如：“myschema ”。 GaussDB(DWS) 数据表：该通道数据的GaussDB(DWS) 数据库模式下的数据表。例如：“mytable”。 数据分隔符：用户数据的字段分隔符，根据此分隔符分隔用户数据插入GaussDB(DWS) 数据表的相应列。用户名：待转储的GaussDB(DWS) 目标数据库的用户名，该数据库用户需要有“GaussDB(DWS) 数据表”的读写权限。例如：“joe”。 密码：“用户名”参数所指定用户的密码。准备DIS应用开发环境，发送实时数据到DIS。在GaussDB(DWS) 数据库中查看从DIS导入的数据1. 使用SQL客户端连接GaussDB(DWS) 集群中已导入DIS数据的数据库。2. 在SQL客户端中执行查询命令，查看从DIS导入GaussDB(DWS) 的数据。命令示例如下，其中table_name请替换为DIS通道转储至GaussDB(DWS) 的目标数据表名。SELECT * FROM table_name;原文链接：https://bbs.huaweicloud.com/blogs/239204【推荐阅读】【最新活动汇总】DWS活动火热进行中，互动好礼送不停（持续更新中）  HOT  【博文汇总】GaussDB(DWS)博文汇总1，欢迎大家交流探讨~（持续更新中）【维护宝典汇总】GaussDB(DWS)维护宝典汇总贴1，欢迎大家交流探讨（持续更新中）【项目实践汇总】GaussDB(DWS)项目实践汇总贴，欢迎大家交流探讨（持续更新中）【DevRun直播汇总】GaussDB(DWS)黑科技直播汇总，欢迎大家交流学习（持续更新中）【培训视频汇总】GaussDB(DWS) 培训视频汇总,欢迎大家交流学习（持续更新中）扫码关注我哦，我在这里↓↓↓

【摘要】 介绍HDFS上数据生成，方便SQL ON HADOOP的模测。Sql ON Anywhere之数据篇1 概述    当前用于大数据处理的引擎组件种类繁多，且各自提供了丰富的接口供用户使用。但对传统数据库用户来说，SQL语言依然是最熟悉和方便的一种接口。如果能在一个客户端中使用SQL语句操作不同的大数据组件，将极大提升使用各种大数据组件的效率。GaussDB(DWS)支持SQL on Anywhere，基于GaussDB(DWS)可以操作OBS、Hadoop、Oracle、Spark和other GaussDB(DWS)，构筑起统一的大数据计算平台。主要包括基于文件系统(HDFS和OBS，狭义的SQL On Anywhere)和其他异构数据库的交互(ORACLE、SPARK和Other GaussDB)。基于文件系统的访问主要通过Foreign Table或者ELK机制的跨集群访问数据，与其他异构数据库主要通过EC+ODBC的方式访问。SQL on Anywhere相关的介绍分几期介绍，此篇主要介绍基于HADOOP的数据生成、下载和上传，方便读写的模测。目前支持的格式主要是txt、csv、parquet、orc。2 HDFS上数据上传下载（1）安装hdfs客户端source /opt/hadoopclient/bigdata_envkinit hdfsPassword for hdfs@HADOOP.COM:（输入密码）（2）查看数据hdfs dfs -ls /user/hive/warehouse/（3）拷贝HDFS数据hdfs dfs -cp  /user/hive/warehouse/mppdb/region /user/hive/warehouse/hdfsdata（4）get数据到本地hdfs dfs -get  /user/hive/warehouse/mppdb/region /data1（5）上传数据至HDFShdfs dfs -put  /data1/region /user/hive/warehouse/mppdb/region3 TXT/CSV数据生成    TXT/CSV格式可以通过第三方工具或者自己写程序构造，上传至hadoop或者在借助Hive生成，Hive生成类似parquet数据成功，在parquet章节介绍。4 orc格式数据生成4.1借助hdfs外表导出功能生成orc数据（1）创建一张行存表region，并插入数据（2）创建一张同结构的hdfs写外表CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS   (address 'xx,    hdfscfgpath 'xx',    type 'HDFS') ;CREATE FOREIGN TABLE ft_wo_region(    like region)SERVER    hdfs_serverOPTIONS(    FORMAT 'orc',    encoding 'utf8',    FOLDERNAME '/user/hive/warehouse/mppdb/regin_orc/')WRITE ONLY;（3）导出orc数据INSERT INTO ft_wo_regin SELECT * FROM region;（4）orc数据存储于HDFS的/user/hive/warehouse/mppdb/regin_orc目录下4.2通过Hive生成orc数据生成步骤参考parquet数据生成。5 parquet格式数据生成5.1生成少量的数据在Hive上创建parquet的表，手动插入数据，生成的数据存储于HDFS（1）创建parquet表drop table pt_region;create table pt region( R_REGIONKEY INT,    R_NAME      string,    R_COMMENT string)stored as parquet;（2）插入数据INSERT INTO pt_region VALUES (1,’gaoxin’,’city’);（3）数据存在于HDFS /user/hive/warehouse/pt_region5.2 利用INSERT SELTCT其它格式表生成大量的数据（1）将text数据put到hive上hdfs dfs –put /mnt/data/customer_address /user/hive/warehouse/（2）hive上创建text表（source /opt/hadoopclient/bigdata_env; kinit hdfs;beeline登录hive）create table txt_customer_address(    ca_address_sk             int               ,    ca_address_id             char(16)              ,    ca_street_number          char(10)                      ,    ca_street_name            varchar(60)                   ,    ca_street_type            char(15)                      ,    ca_suite_number           char(10)                      ,    ca_city                   varchar(60)                   ,    ca_county                 varchar(30)                   ,    ca_state                  char(2)                       ,    ca_zip                    char(10)                      ,    ca_country                varchar(20)                   ,    ca_gmt_offset             decimal(5,2)                  ,    ca_location_type          char(20)                    )row format delimited fields terminated by ',' stored as textfile ;（3）load数据load data inpath  '/user/hive/warehouse/customer_address' into table txt_customer_address;（4）创建parquet格式表drop table pt_customer_address;create table pt_customer_address(    ca_address_sk             int               ,    ca_address_id             char(16)              ,    ca_street_number          char(10)                      ,    ca_street_name            varchar(60)                   ,    ca_street_type            char(15)                      ,    ca_suite_number           char(10)                      ,    ca_city                   varchar(60)                   ,    ca_county                 varchar(30)                   ,    ca_state                  char(2)                       ,    ca_zip                    char(10)                      ,    ca_country                varchar(20)                   ,    ca_gmt_offset             decimal(5,2)                  ,    ca_location_type          char(20)                    )stored as parquet;（5）通过insert parquet表 select from text表的方式导入数据insert into pt_customer_address select * from txt_customer_address;（6）在hdfs上查看该parquet格式的表数据hdfs dfs -ls /user/hive/warehouse/pt_customer_address5.3带分区的parquet数据生成（1）hive上创建orc表，并load数据drop table orc_web_site;create table orc_web_site(    web_site_sk               int               ,    web_site_id               char(16)              ,    web_rec_start_date        timestamp                          ,    web_rec_end_date          timestamp                          ,    web_name                  varchar(50)                   ,    web_open_date_sk          int                       ,    web_close_date_sk         int                       ,    web_class                 varchar(50)                   ,    web_manager               varchar(40)                   ,    web_mkt_id                int                       ,    web_mkt_class             varchar(50)                   ,    web_mkt_desc              varchar(100)                  ,    web_market_manager        varchar(40)                   ,    web_company_id            int                      ,    web_company_name          char(50)                      ,    web_street_number         char(10)                      ,    web_street_name           varchar(60)                   ,    web_street_type           char(15)                      ,    web_suite_number          char(10)                      ,    web_city                  varchar(60)                   ,    web_county                varchar(30)                   ,    web_state                 char(2)                       ,    web_zip                   char(10)                      ,    web_country               varchar(20)                   ,    web_gmt_offset            decimal(5,2)                  ,    web_tax_percentage        decimal(5,2)                 )stored as orc;load data inpath  '/user/hive/warehouse/hdfs/public.orc_web_site' into table orc_web_site;（2）创建parquet分区表，web_rec_start_date列为分区表（hive上分区列也是也是表的一列，不能在create table()中重复创建）drop table pt_web_site;create table pt_web_site(    web_site_sk               int               ,    web_site_id               char(16)              ,    web_rec_end_date          timestamp                          ,    web_name                  varchar(50)                   ,    web_open_date_sk          int                       ,    web_close_date_sk         int                       ,    web_class                 varchar(50)                   ,    web_manager               varchar(40)                   ,    web_mkt_id                int                       ,    web_mkt_class             varchar(50)                   ,    web_mkt_desc              varchar(100)                  ,    web_market_manager        varchar(40)                   ,    web_company_id            int                       ,    web_company_name          char(50)                      ,    web_street_number         char(10)                      ,    web_street_name           varchar(60)                   ,    web_street_type           char(15)                      ,    web_suite_number          char(10)                      ,    web_city                  varchar(60)                   ,    web_county                varchar(30)                   ,    web_state                 char(2)                       ,    web_zip                   char(10)                      ,    web_country               varchar(20)                   ,    web_gmt_offset            decimal(5,2)                  ,    web_tax_percentage        decimal(5,2)                 )PARTITIONED BY(web_rec_start_date timestamp)stored as parquet;（3）静态分区导入方式，适合分区列distinct值少量的，比如该分区列只有2个值1997-08-16 00:00:001999-08-17 00:00:00导入方式如下，insert pt_table PARTITION (web_rec_start_date='1997-08-16 00:00:00')  select指定目标列where web_rec_start_date='1997-08-16 00:00:00'INSERT OVERWRITE TABLE pt_web_site PARTITION (web_rec_start_date='1997-08-16 00:00:00') select web_site_sk , web_site_id ,web_rec_end_date,web_name , web_open_date_sk , web_close_date_sk ,web_class,web_manager ,web_mkt_id,web_mkt_class,web_mkt_desc,web_market_manager ,web_company_id ,web_company_name ,  web_street_number ,web_street_name ,web_street_type ,web_suite_number ,web_city ,web_county ,web_state  , web_zip  , web_country ,web_gmt_offset  ,web_tax_percentage FROM orc_web_site where web_rec_start_date='1997-08-16 00:00:00';INSERT OVERWRITE TABLE pt_web_site PARTITION (web_rec_start_date='1999-08-17 00:00:00') select web_site_sk , web_site_id ,web_rec_end_date,web_name , web_open_date_sk , web_close_date_sk ,web_class,web_manager ,web_mkt_id,web_mkt_class,web_mkt_desc,web_market_manager ,web_company_id ,web_company_name ,  web_street_number ,web_street_name ,web_street_type ,web_suite_number ,web_city ,web_county ,web_state  , web_zip  , web_country ,web_gmt_offset  ,web_tax_percentage FROM orc_web_site where web_rec_start_date='1999-08-17 00:00:00';（4）通过动态分区方式导入Hive上设置参数：set hive.exec.dynamic.partition.mode=nostrick;否则主分区不允许动态分区INSERT OVERWRITE TABLE pt_web_site PARTITION (web_rec_start_date) select web_site_sk , web_site_id ,web_rec_end_date,web_name , web_open_date_sk , web_close_date_sk ,web_class,web_manager ,web_mkt_id,web_mkt_class,web_mkt_desc,web_market_manager ,web_company_id ,web_company_name ,  web_street_number ,web_street_name ,web_street_type ,web_suite_number ,web_city ,web_county ,web_state  , web_zip  , web_country ,web_gmt_offset  ,web_tax_percentage,web_rec_start_date FROM orc_web_site ;（5）将hive数据get到本地hdfs dfs -get /user/hive/warehouse/pt_customer_address /mnt/data原文链接：https://bbs.huaweicloud.com/blogs/237984【推荐阅读】【最新活动汇总】DWS活动火热进行中，互动好礼送不停（持续更新中）  HOT  【博文汇总】GaussDB(DWS)博文汇总1，欢迎大家交流探讨~（持续更新中）【维护宝典汇总】GaussDB(DWS)维护宝典汇总贴1，欢迎大家交流探讨（持续更新中）【项目实践汇总】GaussDB(DWS)项目实践汇总贴，欢迎大家交流探讨（持续更新中）【DevRun直播汇总】GaussDB(DWS)黑科技直播汇总，欢迎大家交流学习（持续更新中）【培训视频汇总】GaussDB(DWS) 培训视频汇总,欢迎大家交流学习（持续更新中）扫码关注我哦，我在这里↓↓↓

【摘要】 本文主要分享在大数据场景数据覆盖式导入数据库的方法。前言众所周知，数据库中INSERT INTO语法是append方式的插入，而最近在处理一些客户数据导入场景时，经常遇到需要覆盖式导入的情况，常见的覆盖式导入主要有下面两种：1、部分覆盖：新老数据根据关键列值匹配，能匹配上则使用新数据覆盖，匹配不上则直接插入。2、完全覆盖：直接删除所有老数据，插入新数据。本文主要介绍如何在数据库中完成覆盖式数据导入的方法。部分覆盖业务场景某业务每天给业务表中导入大数据进行分析，业务表中某列存在主键，当插入数据和已有数据存在主键冲突时，希望能够对该行数据使用新数据覆盖或者说更新，而当新老数据userid不冲突的情况下，直接将新数据插入到数据库中。以将表src中的数据覆盖式导入业务表des中为例：应用方案方案一：使用DELETE+INSERT组合实现（UPDATE也可以，请读者思考）--开启事务START TRANSACTION;--去除主键冲突数据DELETE FROM desUSING srcWHERE EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);--导入新数据INSERT INTO desSELECT *FROM srcWHERE NOT EXISTS (SELECT 1 FROM des WHERE des.userid = src.userid);--事务提交COMMIT;方案优点：使用最常见的使用DELETE和INSERT即可实现。方案缺点：1、分了DELETE和INSERT两个步骤，易用性欠缺；2、借助子查询识重，DELETE/INSERT性能受查询性能制约。 方案二：使用MERGE INTO功能实现MERGE INTO des USING src ON (des.userid = src.userid)WHEN MATCHED THEN UPDATE SET des.b = src.bWHEN NOT MATCHED THEN INSERT VALUES (src.userid,src.b);方案优点：MERGE INTO单SQL搞定，使用便捷，内部去重效率高。方案缺点：需要数据库产品支持MERGE INTO功能，当前Oracle、GaussDB(DWS)等数据库已支持此功能，mysql的insert into on duplicate key也类似此功能。完全覆盖业务场景某业务每天给业务表中导入一定时间区间的数据进行分析，分析只需要导入时间区间的去除，不需要以往历史数据，这种情况就需要使用到覆盖式导入。应用方案方案一：使用TRUNCATE+INSERT组合实现--开启事务START TRANSACTION;--清除业务表数据TRUNCATE des;--插入1月份数据INSERT INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';--提交事务COMMIT;方案优点：简单暴力，先清理在插入直接实现类似覆盖写功能。方案缺点：TRUNCATE清理业务表des数据时对表加8级锁直到事务结束，在因数据量巨大而INSERT时间很长的情况下，des表在很长时间内是不可访问的状态，业务表des相关的业务处于中断状态。 方案二：使用创建临时表过渡的方式实现--开启事务START TRANSACTION;--创建临时表CREATE TABLE temp(LIKE desc INCLUDING ALL);--数据先导入到临时表中INSERT INTO temp SELECT * FROM src WHERE TIME > '2020-01-01 00:00:00' AND TIME < '2020-02-01 00:00:00';--导入完成后删除业务表desDROP TABLE des;--修改临时表名temp->desALTER TABLE temp RENAME TO des;--提交事务COMMIT;方案优点：相比方案一，在INSERT期间，业务表des可以继续被访问（老数据），即事务提交前分析业务可继续访问老数据，事务提交后分析业务可以访问新导入的数据。方案缺点：1、组合步骤较多，不易用；2、DROP TABLE操作会删除表的依赖对象，例如视图等，后面依赖对象的还原可能会比较复杂。 方案三：使用INSERT OVERWRITE功能INSERT OVERWRITE INTO des SELECT * FROM src WHERE time > '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';方案优点：单条SQL搞定，执行便捷，能够支持一键式切换业务查询的新老数据，业务不中断。方案缺点：需要产品支持INSERT OVERWRITE功能，当前impala、GaussDB(DWS)等数据库均已支持此功能。 总结随着大数据的场景越来越多，数据导入的场景也越来越丰富，除了本文介绍的覆盖式数据导入，还有其他诸如忽略冲突的INSERT IGNORE导入等等其他的导入方式，这些导入场景可以以使用基础的INSERT、UPDATE、DELETE、TRUNCATE来组合实现，但是也同样会对高级的一键SQL功能有直接诉求，后面有机会再继续介绍。原文链接：https://bbs.huaweicloud.com/blogs/237720【推荐阅读】【最新活动汇总】DWS活动火热进行中，互动好礼送不停（持续更新中）  HOT  【博文汇总】GaussDB(DWS)博文汇总1，欢迎大家交流探讨~（持续更新中）【维护宝典汇总】GaussDB(DWS)维护宝典汇总贴1，欢迎大家交流探讨（持续更新中）【项目实践汇总】GaussDB(DWS)项目实践汇总贴，欢迎大家交流探讨（持续更新中）【DevRun直播汇总】GaussDB(DWS)黑科技直播汇总，欢迎大家交流学习（持续更新中）【培训视频汇总】GaussDB(DWS) 培训视频汇总,欢迎大家交流学习（持续更新中）扫码关注我哦，我在这里↓↓↓