Search code examples
pythongoogle-bigquerypython-bigquery

How to fix: BigQuery query in Python code

I tried to use variables in a BigQuery query. Syntax error. What could be the problem?

I tried string blocks with ''' but with same result.


QUERY = ('WITH activity_data AS ('
          'SELECT user_pseudo_id, event_timestamp, event_name,' 
          'UNIX_MICROS(TIMESTAMP("'+ cohort_date_str'\", "Europe/Budapest")) AS start_day,'
          '3600*1000*1000*24*1 AS one_interval_micros'
          'FROM `events_*`'
          "WHERE _table_suffix BETWEEN \'"+ table_start_str "\' AND \'"+ table_end_str1 "\'"  
          'AND event_name IN ("z_Added","y_Added","x_Added")'
          'AND user_pseudo_id IN ('
                                    'SELECT user_pseudo_id'
                                    'FROM `events_*`' 
                                    "WHERE _table_suffix BETWEEN \'"+ table_start_str"\' AND \'"+ table_end_str2"\'"
                                    'AND event_name = "first_open"'
                                    'AND event_timestamp BETWEEN UNIX_MICROS(TIMESTAMP("'+ cohort_date_str'", "Europe/Budapest"))'
                                    'AND UNIX_MICROS(TIMESTAMP("'+ cohort_date_end_str'", "Europe/Budapest"))'

 """      
                                 )
                                )
...
 """

Syntax error in line 18: The little arrow in the Colab points the dash at the end of the line.

LINE 18:
 'UNIX_MICROS(TIMESTAMP("'+ cohort_date_str'\", "Europe/Budapest")) AS start_day,'

Screenshot about the error: https://image.prntscr.com/image/rpuNg96iRom9-n7qaTvILA.png

Solution

  • You are missing + after + cohort_date_str . same for + table_start_str . etc.