GaussDB(DWS)数据库-开发应用篇

DWS驱动及ODBC/JDBC开发

1. DWS驱动：
   • 驱动概念：数据库驱动是应用程序和数据库存储之间的一种接口，数据库厂商为某一开发语言环境，能够实现数据库调用而开发的类似“翻译员”功能的程序，将复杂的数据库操作和通讯抽象成为当前开发语言的访问接口。
2. 支持驱动类型：JDBC、ODBC等，如果同时拥有不同版本的集群或者当前没有集群，单击“下载”时下载的依然是与现有集群版本相匹配的。
3. 驱动支持平台：JDBC没有平台限制，ODBC有支持平台限制（平台：X86、鲲鹏）
4. JDBC应用程序开发：
   • JDBC整体架构（4层）：应用程序->JDBC Driver Interface->JDBC驱动->数据库。JAVA本身具有良好的平台移植性，这也直接导致JDBC的平台移植性比ODBC强很多。
   • JDBC安全配置：配置JDBC包->加载驱动->连接数据库
     • gsjdbc4.jar:与PostgreSQL保持兼容的驱动包，其中类名、类结构与PostgreSQL驱动完全一致，曾经运行于PostgreSQL的应用程序可以直接移植到当前系统使用。主类名为“org.postgresql.Driver”，数据库连接的URL前缀为“jdbc:postgresql”
     • gsjdbc200.jar:如果同一JVM进程内需要同时访问PostgreSQL及GaussDB(DWS)，请使用此驱动包。主类名为“com.huawei.gauss200.jdbc.Driver”,数据库连接的URL前缀为“jdbc:guassdb”，其余与gsjdbc4.jar相同。
   • 配置注意事项：
     • 连接参数：第三方工具通过JDBC连接DWS时，jdbc向DWS发起连接请求，会默认添加以下配置参数，详见jdbc代码ConnectionFactoryImpl类的实现。这些参数可能会导致JDBC客户端的行为与gsql客户端的行为不一致。如果实际期望和这些配置不符，建议在Java连接设置代码中显示设定这些参数。
     • fetchsize:在应用程序中，如果需要使用fetchsize，必须关闭autocommit。开启autocommit，会令fetchsize失效。
     • autocommit:在jdbc向DWS申请连接的代码中，建议显示打开autocommit开关。如果基于性能或者其他方面考虑，需要关闭autocommit时，需要应用程序自己来保证事务的提交。例如，在指定的业务SQL执行完之后做显式提交，特别是客户端退出之前务必保证所有的事务已经提交。
     • CopyManager:在不使用ETL工具，数据入库实时性要求又比较高的情况下，建议在开发应用程序时，使用dws的jdbc驱动的CopyManager接口进行微批导入。
     • 释放连接：推荐使用连接池来限制应用程序的连接数。每执行一条SQL就连接一次数据库，是一种不好的SQL编写习惯。在应用程序完成作业任务之后，应当及时断开和dws的连接，释放资源。建议在任务中设置session超时时间参数。
     • 使用jdbc连接池，在将连接释放给连接池前，需要执行以下操作，重置会话环境。否则，可能会因为历史会话信息导致的对象冲突。如果在连接中设置了GUC参数，那么在将连接归还连接池之前，必须使用“SET SESSION AUTHORIZATION DEFAULT;RESET ALL;”将连接的状态清空。如果使用了临时表，那么在将连接归还连接池之前，必须将临时表删除。（常见报错：relation “xxx_tmp” already exists）
5. ODBC应用程序开发：
   • ODBC整体架构（5层）：应用程序->标准接口（ODBC API）-> 驱动程序管理器（ODBC Driver Manager）->ODBC驱动->数据库
   • 数据源配置：Linux、windows
   • Linux-ODBC数据源配置：
     • 步骤（4个）：
       • 安装ODBC驱动管理器：获取UnixODBC源码包，编译安装驱动管理器。备注：驱动默认安装在“/usr/local”目录下，生成数据源文件到“/usr/local/etc”目录下，库文件生成在“/usr/local/lib”目录下
       • 配置驱动
       • 安全组配置
       • 测试连接：使用isql -v GaussODBC(数据源名称)
   • windows-ODBC数据源配置：
     • 步骤
       • 配置服务器：与Linux相同
       • 配置数据源（Windows自带驱动管理器，无需额外安装）
   • 应用程序调试：
     • ODBC应用程序调试需要在前面配置正确可用的环境下进行。
6. 客户端连接管理
   • 功能描述：JDBC和ODBC驱动在连接成功后会设置session级GUC参数connection_info.该参数包含连接数据库的驱动类型、驱动版本号、当前驱动的部署路径和进程属主用户，使用json格式记录。默认只显示driver_name和driver_version，driver_path和os_user的显示由用户控制，控制连接参数为ConnectionExtraInfo.connetion_info可以在pg_stat_activity和pgxc_stat_activity中查看。
   • 连接配置示例：
     • JDBC连接：在连接URL中增加connectionExtraInfo参数。
     • ODBC连接：在“/usr/local/etc/odbc.ini”文件中追加ConnectionExtraInfo设置。

SQL编辑器

1. SQL登录：IAM用户一键登录、自定义数据源登录
   • IAM用户一键登录：
     • 用户可以直接使用IAM账号登录集群数据库，免除填写账号密码操作
     • 提供集群列表树展示当前用户拥有的所有集群，可以选择某一个集群直接双击打开集群。
     • 当前连接是用户的IAM账号直接登录，一键登录集群后，用户可以对账号进行赋权。
     • 登录后，就可以对集群数据库做一些开发运维等操作。
     • 限制要求：
       • 使用IAM账号登录，首先需要有DWS Database Access角色权限，以及当前用户必须是子账号。
       • 对于集群版本有一些要求，集群版本要高于8.3.1.330.
   • 自定义数据源登录：
     • 自定义数据源和传递连接方式类似，用户选择集群后，需要填用户名密码登录
     • 在创建连接之前会先提示测试连接，测试正常后可以正常保存。
     • 限制要求：
       • 自定义连接对集群版本没有限制，有的只是一些语法上的兼容性，需要用户自定义根据集群版本来编写SQL
       • 数据源名称如果不填写，会根据集群名（用户名）来创建，注意一个用户下名称不可以重复
2. 元数据管理：
   • 提供了库、模式、表等图形化界面管理，而且以树形方式来层级查看。
   • 提供树形结构来展现库、模式、表、索引等元数据列表，可以层级打开查看。
   • 每层节点也提供右键菜单做新增、修改、删除等操作。
   • 表提供批量操作列，索引，分区，约束信息。
   • 表，分区，视图支持直接打开数据操作，支持根据SQL条件过滤，表和分区还支持单条数据插入，修改和新增。
3. SQL分析执行：
   • SQL诊断军规，SQL拦截规则：
     • NULL校验：NULL值的比较只能使用IS [NOT] NULL方式，其他任何形式的逻辑判断都返回NULL。例如NULL <> NULL,NULL = NULL和NULL <> 1 返回结果都是NULL.
     • COUNT(col)：count(1) 会统计NULL值（真实行数），而count（col）不会统计。
     • LIMIT和ORDER BY : DWS的分布式操作会导致数据跨节点流动，不带ORDER BY 的LIMIT 操作的，会导致输出结果随机。因此除非不关注结果集的稳定性，否则禁止不带ORDER BY 的LIMIT操作。
     • 不稳定函数：子查询中不能出现uuid_generate_v1(), sys_guid(),nextval等不稳定函数，会造成结果集的不稳定。
     • NOT IN 校验：not in 子查询逻辑执行计划，走的是nestloop嵌套循环，在数据量大的情况下，非常容易出现性能问题；通过改写not exists之后，执行计划可以使用hashjoin哈希关联，性能能够得到极大的提升。
     • join 代替exists: join 相比exists和in 具有更好的代码阅读性，SQL优化器相对更容易找到更精确的执行计划。
     • select * : 不建议使用“select * ”这种写法，请明确指定列。
     • ORDER BY: 子查询中禁止使用order by
     • RETURNING: returning会导致语句不下推
     • DISTINCT ON: distinct on 会导致语句不下推
     • 表关联数量：优化器是基于代价的优化器，表数据量越多，估算的偏差就越大，产生性能差的执行计划的风险就越大，所以每条SQL语句中关联的表个数不超过16个
     • schema: 访问表需要加schema。
4. 脚本管理
   • 目录：支持使用目录来管理SQL脚本，最多支持二级目录，每级目录支持创建10个文件夹，用户只能看到自己创建的目录和脚本。
   • 脚本：脚本保存到OBS桶中，可以在基础设置中设置默认桶地址，可选择目录进行管理，目录地址也会默认带到桶地址中。

数据集成工具

1. 实时同步服务：
   • 定义： 实时同步服务是DWS 团队根据特性孵化出的，一个简便易用、高性能的、从Kafka同步数据到DWS的服务化工具。其入库时可采用DWS内部协议，以减少对DWS集群的资源消耗，同步提升入库性能。
2. 创建实时同步服务实例：
   • 创建时填写对应参数，然后点击立即购买，等待创建完成后，就会按需在后台创建好一个资源池，后续运行的作业将会从资源池中分配一定资源供作业运行；资源池统一使用CU分配资源，规格中的CPU数量即为CU数量，内存不可直接控制，他们会按照CPU内存比按照比例分配，例如创建的实例规格为4U16GB,创建时选择3节点，那么总资源 = 4 CU * 3 = 12CU,每个CU内存大学4GB.
3. 实时同步服务连接配置：
   • 连接配置用于配置Kafka、DWS的连接信息，一份配置所有作业均可使用，以便于后面在提交作业时可以直接使用，不用每个作业都配置一份。
   • 当前支持Kafka和dws两种类型的连接，连接名称只用于业务区分，无实际含义，Kafka的服务地址需要是带端口的连接串，并且保证是VPC能访问的IP;dws的连接地址是一个包含协议的完整JDBC连接串，同时也需要是能在VPC内能访问的IP.

数据调度工具

1. Airflow基础：
   • 定义：Apache Airflow是一个开源平台，用于开发、调度和监控，面向批处理的工作流程。
   • 是可扩展Python框架，主要特点是所有工作流都用Python代码定义，有如下优点：
     • 动态：Airflow管道配置为Python代码，允许动态管道生成。
     • 可扩展：Airflow框架包含可连接多种技术的运算符。所有Airflow组件都可扩展，可轻松适应运行环境。
     • 灵活：利用Jinja模板引擎，内置工作流参数化。
   • 架构组件（5）：分布式架构
     • Worker: 执行分配的任务
     • Scheduler: 负责将必要的任务添加到队列中
     • Web Server: HTTP服务器，提供对DAG/任务状态等信息的访问
     • Database: 存储有关任务、DAG、变量、连接等状态的信息
     • Celery: Broker、Result backend
     • Broker: 存储要执行的任务。
     • Result backend : 存储已完成任务的状态。
   • Airflow删除：当用户不需要使用某个集群时，可以删除该集群。删除的集群无法恢复，同时集群中的用户数据，已执行任务的历史记录，也会自动删除，而且无法再访问。删除集群时，不会删除集群使用的DAG文件。
2. 注意要点：
   • 访问DWS时，建议在Airflow中新建连接，通过base_hook获取连接ID，获取对应数仓的连接信息。避免在DAG文件中，硬编码数仓密码。

基于公开来源信息, 学习资源来自华为云GaussDB(DWS)数据库官网