1

I am getting JSON response from a API. I want to get 5 columns from that,from which 4 are normal,but 1 column in RECORD REPEATED type. I want to load that data to Bigquery table. Below is my code in which Schema is mentioned.

import requests from requests.auth import HTTPBasicAuth import json from google.cloud import bigquery

import pandas
import pandas_gbq



URL='<API>'

auth = HTTPBasicAuth('username', 'password')
  
# sending get request and saving the response as response object
r = requests.get(url=URL ,auth=auth)
  

data = r.json()

----------------------json repsonse----------------

{ "data": { "id": "jfp695q8", "origin": "taste", "title": "Christmas pudding martini recipe", "subtitle": null, "customTitles": [{ "name": "editorial", "value": "Christmas pudding martini" }] } }


id=data['data']['id']
origin=data['data']['origin']
title=data['data']['title']
subtitle=data['data']['subtitle']
customTitles=json.dumps(data['data']['customTitles'])
# print(customTitles)

df = pandas.DataFrame(
    {
'id':id,
'origin':origin,
'title':title,
'subtitle':'subtitle',
'customTitles':customTitles

    },index=[0]
)

# df.head()


client = bigquery.Client(project='ncau-data-newsquery-sit')
table_id = 'sdm_adpoint.testfapi'

job_config = bigquery.LoadJobConfig(
    schema=[
    bigquery.SchemaField("id", "STRING"),
    bigquery.SchemaField("origin", "STRING"),
    bigquery.SchemaField("title", "STRING"),
    bigquery.SchemaField("subtitle", "STRING"),

bigquery.SchemaField(
        "customTitles",
        "RECORD",
        mode="REPEATED",
        fields=[
            bigquery.SchemaField("name", "STRING", mode="NULLABLE"),
            bigquery.SchemaField("value", "STRING", mode="NULLABLE"),



])

    ],
        autodetect=False

    )
df.head()
job = client.load_table_from_dataframe(
    df, table_id, job_config=job_config
)

job.result()

customeTitle is RECORD REPEATED fiels, which has two keys name and values, so I have made schema like that. Below is my table schema. table schema

Below is output of df.head()

jfp695q8 taste Christmas pudding martini recipe subtitle [{"name": "editorial", "value": "Christmas pudding martini"}] df.head

Till here its good. But ,when I try to load the data to table it throws below error.

ArrowTypeError: Could not convert '[' with type str: was expecting tuple of (key, value) pair

Can anyone tell me whats wrong here?

1
  • Does this stack link help you? Commented Jan 28, 2023 at 14:06

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.