摘要:当将熊猫数据附加到types中时,不同的BigQuery会导致日常ETL过程中的问题。
我正在开发一个带有气流的直进ETL :每天从API中提取数据,返回( GCS )中JSON文件中的原始数据,然后将来自GCS的数据附加到BigQuery数据库中。我对ETL的extract部分做得不错,调用API并将每个API调用的结果(这将是数据库表中的一行)保存为GCS中自己的JSON对象。对于BigQuery中有1K行的表,我将首先创建/保存1K单独的对象,保存到GCS中的桶中,每个对象都是一个API调用的结果。
我现在正与ETL的load部分做斗争。到目前为止,我已经编写了以下脚本来完成从GCS到BQ的转换
# load libraries, connect to google
from google.cloud import storage
import os
import gcsfs
import json
os.environ['GOOGLE_APPLICATION_CREDENTIALS'] = '/path/to/my/credentials'
# transfer data
def load_directory_to_bq():
# get list of filenames from GCS directory
client = storage.Client()
files = []
blobs = client.list_blobs('my-gcs-bucket', prefix='gcs-path-to-files')
for blob in blobs:
files.append(f'my-gcs-bucket/{blob.name}')
# approach A: This loop pulls json, converts into df, writes to BigQuery, each 1 file at a time
fs = gcsfs.GCSFileSystem() # GCP's Google Cloud Storage (GCS) File System (FS)
for file in files:
with fs.open(file, 'r') as f:
gcs_data = json.loads(f.read())
data = [gcs_data] if isinstance(gcs_data, dict) else gcs_data
this_df = pd.DataFrame(data)
pd.DataFrame.to_gbq(this_df, 'my-bq-tablename', project_id='my-gcp-project-id', if_exists='append')
# approach B: This loop loops all the files, creates 1 large dataframe, and does 1 large insert into BigQuery
output_df = pd.DataFrame()
fs = gcsfs.GCSFileSystem() # GCP's Google Cloud Storage (GCS) File System (FS)
for file in files:
with fs.open(file, 'r') as f:
gcs_data = json.loads(f.read())
data = [gcs_data] if isinstance(gcs_data, dict) else gcs_data
this_df = pd.DataFrame(data)
output_df = output_df.append(this_df)
pd.DataFrame.to_gbq(output_df, 'my-bq-tablename', project_id='my-gcp-project-id', if_exists='append')GCS中的1K对象都是相似的,但并不总是具有完全相同的结构:
但是,对于一些JSON对象,“类型”可以是不同的,对于相同的键,跨不同的对象。当作为1行熊猫数据被加载到python中时,根据值的不同,相同的键key1可能是float或integer。另外,有时对象中缺少一个键,或者它的值/属性是null,这会使“类型”混乱,并在使用to_gbq函数时引起问题。
使用上面的Approach A,当对象/熊猫DF第一次具有不同的类型时,就会抛出以下错误:Please verify that the structure and data types in the DataFrame match the schema of the destination table. Approach A似乎效率低下,因为它为调用to_gbq每个1K行,每次调用都需要2-3秒。
随着熊猫在其B,函数中处理不同的“类型”,将两个数据合并在一起,不同的“类型”问题似乎得到了解决。因此,我获得了1个数据,并可以将其附加到BigQuery中。但是,我仍然担心,将来可能会有新的数据需要追加,而这些数据将与现有表中已经存在的类型不匹配。毕竟,我不是,而是查询旧表的BigQuery,附加到新数据,然后重新创建表。我只是在添加新的行,我担心其中一个键具有不同“类型”的表会导致错误并中断我的管道。
从理论上讲,approach A很好,因为一种可以处理附加到表中的任何单独行而没有错误的方法是好的。但是,它需要确保每个行的键/类型相同。使用B,我不认为python自动将不同类型合并为表的1种类型是不好的,因为这似乎会在新数据输入时引起问题。
我正在考虑这里最好的办法。因为两者都是谷歌的产品,从GCS到BQ应该是直截了当的,但不完善的数据会让它变得更加困难。特别是,我应该在某个地方为每个不同的BQ表定义一个显式的表模式,并编写一个python函数来确保正确的类型/将错误的类型转换为正确的类型?我是否应该每次都在烧烤时重新制作桌子呢?我应该避免所有的Python在一起,并以另一种方式从GCS转移到BQ吗?
发布于 2020-07-21 07:13:33
关于您的方法A和B,我有以下注意事项:
考虑到这一点,我想提出下面的行动。
BigQuery表中的相应字段设置为NULLABLE。df.astype({"key1": float, "key2": int, [...]})中所看到的那样。发布于 2020-09-19 14:13:19
实际上,您需要了解ETL中的转换阶段,因为加载显然只是通过您已经使用的pandas.DataFrame.to_gbq()方法完成的。
让我们看一看ETL流的整体,正如您描述的那样:
资料来源: API -> GCS -> Pandas DataFrame ->目的地: GBQ
注意:
实际上,这里有两个ETL流:
实际上,数据格式变化的根本原因来自您的API,因为它返回JSON作为响应。因为JSON是无模式对象。当然,这种格式的变化会被传递到GCS对象中。另一方面,作为目标,您有GBQ表,该表从创建时刻和之后不能更改开始就具有严格的模式。
因此,为了有效地将来自REST的数据加载到GBQ,您可以遵循以下想法:
- the widest set of possible fields that can be normalized into flat table schema. Like, all optional fields will come at once.
- an arrays in your JSON which are it self complex objects and you need very it lo extract and load. Do with them the step 1.- define the dtypes for your columns explicitly. This allow to avoid problems when pandas dtypes are infered depends on coming data. Note here the the [pandas-gbq documentation](https://pandas-gbq.readthedocs.io/en/stable/writing.html#inferring-the-table-schema)
- arrays naturally will transformed into DataFrame and after you will load all records in one GBQ API call.此外,您还可以重新考虑ETL流。
你刚才说过,GCS目前的作用是:
当您并行地将数据加载到GCS和GBQ中时,所有这些都可以实现。但是,您可以通过一个常见的转换阶段来完成这一任务。
Source: API -> Pandas DataFrame
1. |-> Destination: GBQ (table)
2. |-> Destination: GCS (objects)您可以使用Pandas DataFrame以下列方式执行转换阶段:
实际上,就像你已经做的一样
pd.DataFrame.to_gbq(df,'bq-tablename',project_id='gcp-project-id',if_=‘append’)
#这还可以创建初始的GBQ表,
#类型将像上面提到的熊猫-bgq文档中提到的那样被输入。
。
到GCS,就像你已经做的一样。
https://stackoverflow.com/questions/62987673
复制相似问题