sql - 寻找非云 RDBMS 以导入分区表(CSV 格式)及其目录结构
问题描述
背景:我一直在研究 Cloudera/Impala,以便使用大型数据库并创建更易于管理的“聚合”表,其中包含的信息要少得多。这些更易于管理的表是几十到几百 GB 的数量级,大约有两打表。我正在查看大约 500 GB 的数据,这些数据将适合我实验室的计算机。
问题:我希望使用非云 RDBMS 以便在我的实验室本地进一步处理这些表。原始的 Impala 表(其中大部分按日期分区)已导出为 CSV,这样“表”文件夹包含每个日期的子文件夹,每个子文件夹包含一个唯一的 csv 文件(其中分区的“日期”列不存在,因为它位于其过时的子文件夹中)。哪个是合适的 RDBMS,我将如何导入这些表?
到目前为止我发现了什么:似乎有几个用于 MySQL 的 GUI 或命令可以简化导入,例如:
但是这些并没有解决我的具体情况,因为1.我只能访问集群上的Impala,即我不能添加任何工具,所以必须在实验室计算机上完成繁重的工作,2.他们没有说任何关于导入具有现有目录/分区结构的已分区表。
约束:
- 实验室计算机在 Ubuntu 20.04 上
- 理想情况下,我希望避免手动加载每个 csv / 分区,因为我有数万个日期。我希望有一个已经识别分区目录结构的 RDBMS...
- RDBMS 本身应该有一组相当新的可用函数,包括超前/滞后/第一个/最后一个窗口函数。除此之外,它不必太花哨。
我愿意将 Spark 用作“矫枉过正的 SQL 引擎”,如果这是最好的方法,我只是不太确定这是否是独特计算机(而不是集群)的最佳方法。此外,如果需要(尽管我希望避免这种情况),我可以以另一种格式导出 Impala 表以简化导入阶段。例如,基于文本的表格、镶木地板等的不同格式。
编辑 1 正如评论中所建议的,我目前正在查看 Apache Drill。它已正确安装,并且我已成功运行文档/教程中的基本查询。但是,我现在被困在如何实际“导入”我的表(实际上,我只需要“使用”它们,因为钻似乎能够直接在文件系统上运行查询)我的表。澄清:
- 我目前在目录 /data/table1 和 /data/table2 中有两个“表”。
- 这些目录包含与不同分区对应的子目录,例如: /data/table1/thedate=1995 、 /data/table1/thedate=1996 等,table2 也是如此。
- 在每个子目录中,我都有一个包含 CSV 数据的文件(没有扩展名),没有标题。
我的理解(我对 Apache-Drill 还很陌生)是我需要以某种方式创建一个文件系统存储插件,以便钻头了解在哪里查看以及在查看什么,所以我创建了一个非常基本的插件(准副本/从这个粘贴)使用插件管理页面上的网络界面。这样做的最终结果是,现在我可以打字use data;
和练习了。然后我可以说它show files in data
正确地将 table1 和 table2 列为我的两个目录。不幸的是,我仍然缺少能够成功查询这些表的两个关键事项:
- 运行
select * from data.table1
失败并出现错误,我尝试了 table1 或 dfs.data.table1 并且每个命令都出现不同的错误(找不到对象'data',找不到对象'table1',架构 [[dfs,data]] isnot分别对根模式或当前默认模式有效)。我怀疑这是因为 table1 中有子目录? - 我还没有说任何关于 CSV 文件的结构,并且该结构需要包含子目录名称中存在“thedate”字段和值的事实......
编辑 2 在尝试了很多事情之后,使用基于文本的文件仍然没有运气,但是使用镶木地板文件有效:
我可以查询镶木地板文件
我可以查询包含分区表的目录,每个目录的格式为: thedate=1995 , thedate=1996 如前所述。
我在这里使用了建议以便能够以通常的方式查询表,即不使用
dir0
但使用日期。本质上,我创建了一个视图:创建视图 Drill.test 作为选择
dir0
日期,* 来自 dfs。data/table1_parquet_partitioned
不幸的是,thedate 现在是一个文本,上面写着: thedate=1994 ,而不仅仅是 1994 (int)。所以我重命名了目录以便只包含日期,但这不是一个好的解决方案,因为日期的类型不是 int,因此我不能使用日期来加入 table2(在列中有日期)。所以最后,我所做的是将 thedate 转换为视图中的 int
=> 这一切都很好,虽然不是 csv 文件,但这种替代方案对我来说是可行的。但是我想知道如果使用这样的视图,里面有一个演员,我会从分区修剪中受益吗?引用的stackoverflow链接中的答案表明视图保留了分区修剪,但是当在公式中使用列时我不确定这一点......最后,鉴于我可以完成这项工作的唯一方法是通过镶木地板,它引出了一个问题:就性能而言,钻头是最好的解决方案吗?到目前为止,我喜欢它,但是将数据库迁移到这里会很耗时,我想尝试为此选择最佳目的地,而不需要太多的反复试验......
解决方案
我最终使用了 Spark。我目前知道的唯一替代方案是 Apache Drill,它是由 Simon Darr(我想再次感谢他!)引起我的注意的。据我测试,每种解决方案的优缺点:
- 当数据库以文本形式(在我的例子中是 CSV 文件)导出时,这两种解决方案都不能很好地提供一种简单的方法来导入现有模式。
- 两种解决方案都使用 parquet 文件正确导入模式,因此我决定必须从源集群(使用 Impala)以 parquet 格式重新创建表。
- 剩下的问题是关于分区的:我终于能够弄清楚如何在 Spark 上导入分区文件并且添加分区维度的过程是无缝的(我从这里和这里得到了帮助),而我无法使用 Drill 找到令人信服的方法来做到这一点(尽管按照此处的建议创建视图确实有所帮助):
- 在 Spark 上。我用过:spark.sql("select * from parquet.
file:///mnt/data/SGDATA/sliced_liquidity_parq_part/
")。请注意,重要的是不要像我第一次那样使用 * 通配符,因为如果您使用通配符,每个 parquet 文件都会被读取而不查看它所属的目录,因此它不会考虑目录结构将这些字段分区或添加到架构中。如果没有通配符,则具有语法 field_name=value 的目录名称会正确添加到架构中,并且值类型本身会被正确推断(在我的情况下,是 int,因为我使用了 date=intvalue 语法)。 - 在 Drill 上,创建视图的技巧有点混乱,因为它涉及到,首先,使用子字符串
dir0
来提取 field_name 和 value,其次它需要强制转换才能将该字段发送到正确的类型架构。我真的不确定这种视图是否会在之后进行查询时启用分区修剪,所以我不喜欢这种 hack。注意:可能有另一种方法可以正确地做到这一点,我只是没有找到它。
- 在 Spark 上。我用过:spark.sql("select * from parquet.
我一路学习了 Drill(这对于日志和没有已知结构的东西来说似乎很棒),并且了解到如果数据是结构化的,Spark 可以做很多钻头所做的事情(我不知道它可以读取CSV 或 parquet 文件直接无需底层数据库系统)。我也不知道 Spark 如此容易安装在独立机器上:按照此处的步骤后,我只是在我的 bashrc 中创建了一个脚本,它可以一次性启动 master、worker 和 shell(尽管我无法评论为此使用独立计算机的性能,也许 Spark 在这方面做得不好)。过去使用过 spark 一点,考虑到我的选择,这个解决方案对我来说似乎仍然是最好的。如果还有其他解决方案,请继续使用它们,因为我还不会接受我自己的答案(无论如何,我需要几天时间将所有桌子都改成镶木地板)。
推荐阅读
- flutter - 无法从提供者类中获取值
- reactjs - How can I deploy React App to GoDaddy domain
- .net - NuGet 客户端版本需要 3.6.0 或更高版本
- php - 从 HTML 选择 PHP 抓取选项 ID
- php - How to HMAC-S256 encrypt a string in Node JS
- c# - 如何在C#中获取特殊字符的ASCII码
- excel - 为什么 Excel 在更改工作表的 Worksheet_Change 事件上崩溃?
- classification - 机器学习分类
- azure - Azure ARM 模板 - 列出资源
- flutter - Parallelize audio playbacks in Flutter game