使用JDBC查询数据库

砖支持使用JDBC连接到外部数据库。本文提供了配置和使用这些连接的基本语法的例子在Python中,SQL和Scala。

请注意

你可能更喜欢Lakehouse联合查询外部数据库管理系统。看到使用Lakehouse联合运行查询。

合作伙伴连接提供了优化的集成与许多外部外部数据源同步数据。看到砖的合作伙伴的连接是什么?。

重要的

本文中的示例不包括用户名和密码在JDBC url。砖推荐使用秘密数据库存储凭证。例如:

              用户名=dbutils。秘密。得到(范围=“jdbc”,关键=“用户名”)密码=dbutils。秘密。得到(范围=“jdbc”,关键=“密码”)
             

              瓦尔用户名=dbutils。秘密。得到(范围=“jdbc”,关键=“用户名”)瓦尔密码=dbutils。秘密。得到(范围=“jdbc”,关键=“密码”)
             

与SQL参考数据砖的秘密,你必须配置一个火花在集群initilization配置属性。

完整的保密管理的例子,请参阅秘密工作流示例。

建立云连接

砖vpc配置为只允许火花集群。当连接到另一个基础设施,最佳实践是使用VPC凝视。一旦建立了VPC凝视,你可以检查netcat在集群上的实用性。

% sh数控vz < jdbcHostname > < jdbcPort >

与JDBC读取数据

您必须配置设置读取数据使用JDBC。注意,每个数据库使用不同的格式< jdbc url >。

              employees_table=(火花。读。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<表名称>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。负载())
             

              创建临时视图employees_table_vw使用JDBC选项(url“< jdbc url >”,数据表“<表名称>”,用户“<用户名>”,密码“<密码>”)
             

              瓦尔employees_table=火花。读。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<表名称>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。负载()
             

火花自动从数据库表中读取模式和地图类型回到火花SQL类型。

              employees_table。printSchema
             

              描述employees_table_vw
             

              employees_table。printSchema
             

您可以运行查询这个JDBC表:

              显示(employees_table。选择(“年龄”,“工资”)。groupBy(“年龄”)。avg(“工资”))
             

              选择年龄,avg(工资)作为工资从employees_table_vw集团通过年龄
             

              显示(employees_table。选择(“年龄”,“工资”)。groupBy(“年龄”)。avg(“工资”))
             

与JDBC写数据

保存数据表与JDBC使用类似的配置来阅读。看下面的例子:

              (employees_table。写。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<新的表名>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。保存())
             

              创建表new_employees_table使用JDBC选项(url“< jdbc url >”,数据表“<表名称>”,用户“<用户名>”,密码“<密码>”)作为选择*从employees_table_vw
             

              employees_table。写。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<新的表名>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。保存()
             

默认行为试图创建一个新表和抛出一个错误如果一个表的名称已经存在。

您可以将数据附加到现有表使用下面的语法:

              (employees_table。写。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<新的表名>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。模式(“添加”)。保存())
             

              创建表如果不存在new_employees_table使用JDBC选项(url“< jdbc url >”,数据表“<表名称>”,用户“<用户名>”,密码“<密码>”);插入成new_employees_table选择*从employees_table_vw;
             

              employees_table。写。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<新的表名>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。模式(“添加”)。保存()
             

您可以覆盖现有的表使用下面的语法:

              (employees_table。写。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<新的表名>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。模式(“覆盖”)。保存())
             

              创建或取代表new_employees_table使用JDBC选项(url“< jdbc url >”,数据表“<表名称>”,用户“<用户名>”,密码“<密码>”)作为选择*从employees_table_vw;
             

              employees_table。写。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<新的表名>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。模式(“覆盖”)。保存()
             

控制并行JDBC查询

默认情况下,JDBC驱动程序查询源数据库只有一个线程。要提高读取性能的,您需要指定一些选项来控制多少同时砖对数据库的查询。对于小型集群,设置numPartitions选项的数量等于执行人在集群中核心确保所有节点并行查询数据。

警告

设置numPartitions在大型集群高价值可能导致消极的远程数据库的性能,因为太多的同时查询服务将会瓦解。这是特别麻烦的应用程序数据库。设定这个值高于50的警惕。

请注意

加快查询通过选择一列和索引计算的源数据库partitionColumn。

下面的代码示例演示了配置并行集群的八个核心:

              employees_table=(火花。读。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<表名称>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)#一个列,可以使用均匀分布范围的值,可用于并行化。选项(“partitionColumn”,“<分区键>”)# partitionColumn最小值来提取数据。选项(“下界”,“<最小值>”)#最大价值与partitionColumn拉数据。选项(“upperBound”,“<极大值>”)#分区数量将数据分发到。不设置这个非常大的(~数百)。选项(“numPartitions”,8)。负载())
             

              创建临时视图employees_table_vw使用JDBC选项(url“< jdbc url >”,数据表“<表名称>”,用户“<用户名>”,密码“<密码>”,partitionColumn“<分区键>”,下界“<最小值>”,upperBound“<极大值>”,numPartitions8)
             

              瓦尔employees_table=火花。读。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<表名称>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)/ /列,可以使用均匀分布范围的值,可用于并行化。选项(“partitionColumn”,“<分区键>”)/ /最小值为与partitionColumn提取数据。选项(“下界”,“<最小值>”)/ /最大价值为与partitionColumn提取数据。选项(“upperBound”,“<极大值>”)/ /将数据分发到数量的分区。不设置这个非常大的(~数百)。选项(“numPartitions”,8)。负载()
             

请注意

砖支持所有Apache的火花选项配置JDBC。

写入数据库时使用JDBC, Apache火花在内存中使用分区的数量来控制并行性。你可以重新分配数据之前写控制并行。避免大量的分区在大型集群,以避免远程数据库。下面的例子演示了实现下笔前8个分区:

              (employees_table。重新分区(8)。写。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<新的表名>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。保存())
             

              创建表new_employees_table使用JDBC选项(url“< jdbc url >”,数据表“<表名称>”,用户“<用户名>”,密码“<密码>”)作为选择* / / * +重新分区(8)*从employees_table_vw
             

              employees_table。重新分区(8)。写。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<新的表名>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。保存()
             

压低查询数据库引擎

你可以压低整个查询到数据库并返回结果。的表参数识别JDBC表来阅读。您可以使用任何有效的SQL查询从条款。

              pushdown_query=”(select *从员工列出< 10008)作为emp_alias”employees_table=(火花。读。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,pushdown_query)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。负载())
             

              创建临时视图employees_table_vw使用JDBC选项(url“< jdbc url >”,数据表”(select *从员工列出< 10008)作为emp_alias”,用户“<用户名>”,密码“<密码>”)
             

              瓦尔pushdown_query=”(select *从员工列出< 10008)作为emp_alias”瓦尔employees_table=火花。读。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,pushdown_query)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。负载()
             

控制每个查询获取的行数

JDBC驱动程序有一个fetchSize参数控制的行数获取一次从远程数据库。

设置	结果
过低	高延迟由于许多往返(每查询返回几行)
太高了	内存不足错误(在一个查询返回太多的数据)

最优值是依赖于工作的。注意事项包括:

查询返回的列是多少?
返回的数据类型是什么?
返回的字符串在每一列多久?

系统可能非常小的违约和受益于调优。例如:甲骨文的违约fetchSize是10。增加到100减少总需要执行的查询的10倍。JDBC结果是网络流量,所以避免非常大的数字,但最佳值可能是成千上万的许多数据集。

使用fetchSize选项,如以下示例:

              employees_table=(火花。读。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<表名称>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。选项(“fetchSize”,“100”)。负载())
             

              创建临时视图employees_table_vw使用JDBC选项(url“< jdbc url >”,数据表“<表名称>”,用户“<用户名>”,密码“<密码>”。fetchSizeOne hundred.)
             

              瓦尔employees_table=火花。读。格式(“jdbc”)。选项(“url”,“< jdbc url >”)。选项(“数据表”,“<表名称>”)。选项(“用户”,“<用户名>”)。选项(“密码”,“<密码>”)。选项(“fetchSize”,“100”)。负载()
             