CODEWITHSUNDEEP
postgresqlgoogle-bigqueryapache-beamdataflow
tripleb
tripleb

Reputation: 67

BigQuery to Postgre execution failed on Dataflow workflow timestamp

Hi have this issue where am unsure how to get a proper start date for my query, I get the following error and am unsure how to go about fixing it. Can I get help on the time conversion format please?

    apache_beam.runners.dataflow.dataflow_runner.DataflowRuntimeException: Dataflow pipeline failed. State: FAILED, Error:
Workflow failed. Causes: S01:QueryTableStdSQL+Writing to DB/ParDo(_WriteToRelationalDBFn) failed., BigQuery execution failed., Error:
 Message: No matching signature for operator >= for argument types: TIMESTAMP, INT64. Supported signature: ANY >= ANY at [1:1241]
 HTTP Code: 400

My script main query looks like:

with beam.Pipeline(options=options) as p:
rows = p | 'QueryTableStdSQL' >> beam.io.Read(beam.io.BigQuerySource(use_standard_sql=True,
    query = 'SELECT \
    billing_account_id, \
    service.id as service_id, \
    service.description as service_description, \
    sku.id as sku_id, \
    sku.description as sku_description, \
    usage_start_time, \
    usage_end_time, \
    project.id as project_id, \
    project.name as project_description, \
    TO_JSON_STRING(project.labels) \
    as project_labels, \
    project.ancestry_numbers \
    as project_ancestry_numbers, \
    TO_JSON_STRING(labels) as labels, \
    TO_JSON_STRING(system_labels) as system_labels, \
    location.location as location_location, \
    location.country as location_country, \
    location.region as location_region, \
    location.zone as location_zone, \
    export_time, \
    cost, \
    currency, \
    currency_conversion_rate, \
    usage.amount as usage_amount, \
    usage.unit as usage_unit, \
    usage.amount_in_pricing_units as \
    usage_amount_in_pricing_units, \
    usage.pricing_unit as usage_pricing_unit, \
    TO_JSON_STRING(credits) as credits, \
    invoice.month as invoice_month, \
    cost_type, \
    FROM `pprodjectID.bill_usage.gcp_billing_export_v1_xxxxxxxx` \
    WHERE export_time >= 2020-01-01'))
source_config = relational_db.SourceConfiguration(

The date format on bigquery console

export_time
2018-01-25 01:18:55.637 UTC

usage_start_time
2018-01-24 21:23:10.643 UTC

Upvotes: 0

Views: 101

Answers (1)

Pentium10
Pentium10

Reputation: 208002

You forgot to include as a string the time

WHERE export_time >= 2020-01-01

The above results Calc: 0+2020-01-01=2018 you should have

WHERE export_time >= "2020-01-01"

Upvotes: 2

Related Questions