-1

I need to pass dates to my query in BigQuery python API as follow. It runs safely; however, the destination table is not populated as the dates are not successfully passed to the query. I'm not sure what causes the issue.

    client = bigquery.Client()
    job_config = bigquery.QueryJobConfig()

    query = """
            select distinct 
            ga.fullVisitorId
            from `843777.ga_sessions_*` ga, UNNEST(ga.hits) as hits
            where totals.timeOnSite > 0
            and (ga._TABLE_SUFFIX >= @start_date and ga._TABLE_SUFFIX <= @end_date)
    """

    query_params = [
        bigquery.ScalarQueryParameter('start_date', 'STRING', self.start_date),
        bigquery.ScalarQueryParameter('end_date', 'STRING', self.end_date)
    ]

    # Set the destination table
    table_ref = client.dataset("segmentation_project").table('myTable')
    job_config.destination = table_ref
    job_config.allow_large_results = True
    job_config.write_disposition = bigquery.WriteDisposition.WRITE_TRUNCATE
    job_config.query_parameters = query_params

    try:
        query_job = client.query(query, location="US", job_config=job_config) # API request - starts the query
        query_job.result()  # Waits for job to complete.
        print('Query results loaded to table {}'.format(table_ref.path))
    except ValueError:
        print("Unable to load dataset")

In the snippet above, self.start_date and self.end_date are initialized once the object is created:

start_date, end_date = '2018-06-01 00:00:00', '2018-06-30 23:59:59'

I'm aware of this question, too.

user3000538
  • 189
  • 1
  • 2
  • 14

1 Answers1

2

If you look at the format of _TABLE_SUFFIX, it has the form YYYYMMDD, so trying to compare it to YYYY-MM-DD 00:00:00 isn't going to give you the results you want. You should instead declare:

start_date, end_date = '20180601', '20180630'
Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99