How to convert array to string value

Question

Hello i am trying to get my queries log cost, i get the total amount but when i try to break it down per dataset i get this error:

' Cannot access field datasetId on a value with type ARRAY> at '

this is my query i am trying to run:

WITH
  data AS (
  SELECT
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent AS jobCompletedEvent,
    (
    SELECT
      ARRAY_TO_STRING((
        SELECT
          ARRAY_AGG(datasetId)
        FROM
          UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables.datasetId) ))) AS datasetIds
  FROM
    `kkk111.bq_audit_log_export.cloudaudit_googleapis_com_data_access_20190206` )
SELECT
  datasetIds,
  FORMAT('%9.2f',5.0 * (SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes)/POWER(2, 40))) AS Estimated_USD_Cost
FROM
  data
WHERE
  jobCompletedEvent.eventName = 'query_job_completed'
GROUP BY
  datasetIds
ORDER BY
  Estimated_USD_Cost DESC

I am using Standard SQL Dialect
how do i cast this field:

protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables.datasetId

from array to a string ? what am i missing ? Thanks.

Mikhail Berlyant · Accepted Answer · 2019-02-06 16:19:42Z

1

Below is for BigQuery Standard SQL

#standardSQL   
WITH data AS (
  SELECT 
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent AS jobCompletedEvent,
    ref.datasetId AS datasetId
  FROM `kkk111.bq_audit_log_export.cloudaudit_googleapis_com_data_access_20190206`,
  UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables) ref 
)
SELECT
  datasetId,
  FORMAT('%9.2f',5.0 * (SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes)/POWER(2, 40))) AS Estimated_USD_Cost
FROM data
WHERE jobCompletedEvent.eventName = 'query_job_completed'
GROUP BY datasetId
ORDER BY Estimated_USD_Cost DESC

As you can see there, obviously you need to UNNEST referencedTables ARRAY but also you need to make sure your final calculation of Cost is as close to correct one as possible. The same query can reference multiple tables from the same dataset, so you better have DISTINCT in your CTE. But also, same query can reference tables from multiple datasets - so in this same billing bytes will be attributed to multiple datasets, so you will have overestimation! I don't know your exact intent - but you might want to introduce some logic to distribute the cost among the referenced datasets.

edited Feb 6, 2019 at 16:19

answered Feb 6, 2019 at 16:10

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sortof Over a year ago

#Mikhail Berlyant, thanks for helping , i get this error :Column jobCompletedEvent of type STRUCT cannot be used in SELECT DISTINCT at . regarding the table reference its fine because i keep it in separate dataset per client so no worry,

sortof Over a year ago

hallelujah brother !! 8 hours of debugging that ! finally workingg ! thanks a lot dude. much appreciate !

Elliott Brossard · Accepted Answer · 2019-02-06 16:56:47Z

1

You need to UNNEST the outer array in order to select the dataset ID inside:

SELECT
  ARRAY_TO_STRING((
    SELECT ARRAY_AGG(datasetId)
    FROM UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables)
    ), ',') AS datasetIds
FROM ...

edited Feb 6, 2019 at 16:56

answered Feb 6, 2019 at 14:11

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

4 Comments

sortof Over a year ago

#Elliott Brossard , i tired your answer(i edit my question), but i still get : Cannot access field datasetId on a value with type ARRAY>

Elliott Brossard Over a year ago

I had a small mistake in the answer; I meant to omit datasetId from the path. Try it again.

sortof Over a year ago

i change to this: FROM UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.referencedTables))) AS datasetIds ... error returns : No matching signature for function ARRAY_TO_STRING for argument types: ARRAY. Supported signatures: ARRAY_TO_STRING(ARRAY, STRING, [STRING]); ARRAY_TO_STRING(ARRAY, BYTES, [BYTES])

Elliott Brossard Over a year ago

Looks like Mikhail helped you figure out an answer, but the issue here was that the ARRAY_TO_STRING function needs a second argument (the separator).

Collectives™ on Stack Overflow

How to convert array to string value

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related