csv - 按列名解析 Athena 中的 CSV

Question

我正在尝试基于 CSV 文件创建一个外部表。我的问题是并非所有 CSV 文件都是相同的(其中一些缺少列)，并且列的顺序并不总是相同。

问题是我是否可以让 Athena 按名称而不是按顺序解析列

Answer 1

不，athena 不能按名称而不是按顺序解析列。数据的顺序应与表架构中定义的顺序完全相同。在将它们写入 S3 之前，您需要预处理 CSV 并更改列顺序。

从 aws athena 文档中添加引号:

When you create a new table schema in Athena, Athena stores the schema in a data catalog and uses it when you run queries.

Athena uses an approach known as schema-on-read, which means a schema is projected on to your data at the time you execute a query. This eliminates the need for data loading or transformation.

When you create a database and table in Athena, you are simply describing the schema and the location where the table data are located in Amazon S3 for read-time querying. Database and table, therefore, have a slightly different meaning than they do for traditional relational database systems because the data isn't stored along with the schema definition for the database and table.

引用:Tables and databases in athena