CODEWITHSUNDEEP
pythongoogle-bigquerybigdata
Quy Ba Pham
Quy Ba Pham

Reputation: 91

Google Bigquery API: How to add result query to Table?

I have already read some introduction about this topic but this code didn't run. I created table 'product_flat_index' in project and this function add query result to that table. But it didn't run .

def insertValues(service):
    project_id = "598330041668"
    dataset_id = 'recommendation_001'
    table_id = 'product_flat_index'


    # [START run_query]

    query = ('SELECT sku, SUM(sales) AS sales, COUNT(sales) AS sales_frequency, SUM(views) AS views,'
          'SUM(carts) AS carts,  SUM(sales) / SUM(carts) AS sales_effective_rate,AVG(rating) AS rating,'
          'SUM(comments) AS comments '
          'FROM recommendation_001.user_input_product '
          'GROUP BY sku '
             )

    configuration = {
        "query": query,

    }
    body = {
        "configuration":
        {
            "query": configuration,
            "destinationTable": {
                "projectId": project_id,
                "datasetId": dataset_id,
                "tableId": table_id
            },
            "createDisposition": "CREATE_IF_NEEDED",
            "writeDisposition": "WRITE_APPEND",
        },

    }

    return service.jobs().insert(
        projectId=project_id,
        body=body
    ).execute()
    # [END run_query]

Upvotes: 0

Views: 162

Answers (1)

Danny Kitt
Danny Kitt

Reputation: 3251

You should specify your destinationTable, createDisposition, and writeDisposition properties within your query configuration object, not the top-level configuration. They are properties of configuration.query, not the top-level configuration. For example, here's the destinationTable property in our docs: note how it is configuration.query.destinationTable, and not configuration.destinationTable. It's structured similarly for the other fields you're specifying.

I think this should work:

configuration = {
    "query": query,
    "destinationTable": {
        "projectId": project_id,
        "datasetId": dataset_id,
        "tableId": table_id
    },
    "createDisposition": "CREATE_IF_NEEDED",
    "writeDisposition": "WRITE_APPEND"
}
body = {
    "configuration":
    {
        "query": configuration
    }
}

If you want to debug this on your own, I'd suggest looking up the job details using jobs.list or jobs.get to see if your configuration details made it through to the server. I suspect your past jobs that didn't work also won't have the additional properties for destinationTable, etc.

Upvotes: 1

Related Questions