首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >从中提取JSON,转换成熊猫DF,并写信给Google BigQuery

问从中提取JSON,转换成熊猫DF,并写信给Google BigQuery
EN

Stack Overflow用户
提问于 2020-07-20 02:08:12
回答 2查看 1.5K关注 0票数 2

摘要:当将熊猫数据附加到types中时,不同的BigQuery会导致日常ETL过程中的问题。

我正在开发一个带有气流的直进ETL :每天从API中提取数据,返回( GCS )中JSON文件中的原始数据,然后将来自GCS的数据附加到BigQuery数据库中。我对ETL的extract部分做得不错,调用API并将每个API调用的结果(这将是数据库表中的一行)保存为GCS中自己的JSON对象。对于BigQuery中有1K行的表,我将首先创建/保存1K单独的对象,保存到GCS中的桶中,每个对象都是一个API调用的结果。

我现在正与ETL的load部分做斗争。到目前为止,我已经编写了以下脚本来完成从GCS到BQ的转换

代码语言:javascript
复制
# 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吗?

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-07-21 07:13:33

关于您的方法A和B,我有以下注意事项:

  1. 如果请求比较慢,并且有大量的行,那么方法B肯定会工作得更快。
  2. 我不知道您的数据量,但请记住,如果您有大量的数据,您必须注意您的机器容量,以避免不良的性能和错误。
  3. 如果您的进程每天只执行一次,那么将所有数据插入表中所需的时间可能根本不是问题。
  4. 正如您所说的,方法B可以避免模式问题,但没有保证。

考虑到这一点,我想提出下面的行动。

  1. 对于文件中可能丢失信息(或可能为NULL)的键,请将BigQuery表中的相应字段设置为NULLABLE。
  2. 使用A或B两种方法,通过使用一些转换您的Dataframe列的函数,确保Dataframe具有正确的类型。您可以更改Dataframe列的类型,例如,正如您在此df.astype({"key1": float, "key2": int, [...]})中所看到的那样。
票数 1
EN

Stack Overflow用户

发布于 2020-09-19 14:13:19

实际上,您需要了解ETL中的转换阶段,因为加载显然只是通过您已经使用的pandas.DataFrame.to_gbq()方法完成的。

让我们看一看ETL流的整体,正如您描述的那样:

资料来源: API -> GCS -> Pandas DataFrame ->目的地: GBQ

注意:

  • 您在API和GCS之间执行哪些数据转换?

实际上,这里有两个ETL流:

  1. 来源: API -> ??->目的地: GCS (->对象)
  2. 来源: GCS (JSON对象) -> Pandas DataFrame ->目的地: GBQ (表)

实际上,数据格式变化的根本原因来自您的API,因为它返回JSON作为响应。因为JSON是无模式对象。当然,这种格式的变化会被传递到GCS对象中。另一方面,作为目标,您有GBQ表,该表从创建时刻和之后不能更改开始就具有严格的模式。

因此,为了有效地将来自REST的数据加载到GBQ,您可以遵循以下想法:

  1. JSON是一个嵌套的数据结构,而一个表是平面的。所以任务是把第一个转换成第二个。
  2. 通过检查API响应对象并定义
代码语言:javascript
复制
- 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.
  1. 有了这样的平面模式理解,就可以用所有NULLABLE字段创建GBQ表(每个实际提取的对象都有单独的表)。
  2. 如果您使用Pandas DataFrame来转换目的,那么:
代码语言:javascript
复制
- 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目前的作用是:

  • (a)备份原始数据
  • (b)如烧烤或其他地方有问题,可作为原始资料的真实来源。
  • (c)避免在上传到BQ之前,在出现问题时进行两次相同的API调用

当您并行地将数据加载到GCS和GBQ中时,所有这些都可以实现。但是,您可以通过一个常见的转换阶段来完成这一任务。

代码语言:javascript
复制
Source: API -> Pandas DataFrame
    1. |-> Destination: GBQ (table)
    2. |-> Destination: GCS (objects)

您可以使用Pandas DataFrame以下列方式执行转换阶段:

  1. 将JSON对象嵌套到平面表(DataFrame)中: df = pd.json_normalize(api_response_json_object,‘api_response_nested_object’,sep='_')
  2. 力场数据类型: def force_df_schema( df,columns_list,columns_dtypes):df = df.reindex(columns_list,axis=“列”) df = df.astype(columns_dtypes)返回df API_TRANSACTION_OBJECT_COLUMNS = 'c1','c2','c3','c4‘API_TRANSACTION_OBJECT_COLUMNS_DTYPES ={ 'c1':'object','c2':'datetime64ns','c3':’float64 64‘,'c4':'int‘}#这个调用将返回具有# {transaction}嵌套结构的JSON,我们需要提取、转换和加载api_response_json_object = api.call() df = pd.json_normalize(api_response_json_object,’api_response_ nested _object‘,sep='_') df = force_df_schema(df,API_TRANSACTION_OBJECT_COLUMNS,force_df_schema)。API_TRANSACTION_OBJECT_COLUMNS_DTYPES)
  3. 装载到目标存储库:

实际上,就像你已经做的一样

代码语言:javascript
复制

pd.DataFrame.to_gbq(df,'bq-tablename',project_id='gcp-project-id',if_=‘append’)

#这还可以创建初始的GBQ表,

#类型将像上面提到的熊猫-bgq文档中提到的那样被输入。

代码语言:javascript
复制

。

到GCS,就像你已经做的一样。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/62987673

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档