CODEWITHSUNDEEP
sqlgoogle-bigquery
Frederic Bastiat
Frederic Bastiat

Reputation: 693

Correct column name selections

#standardSQL
SELECT
  blockchain.num_tx AS `num_tx`,
  blockchain.timestamp AS `timestamp`,
  GDELT.GLOBALEVENTID AS GLOBALEVENTID,
  PARSE_TIMESTAMP('%Y%m%d', CAST(GDELT.SQLDATE AS STRING)) AS SQLDATE,
  GDELT.Actor1Code AS Actor1Code,
  GDELT.Actor1Name AS Actor1Name,
  GDELT.Actor2Code AS Actor2Code,
  GDELT.Actor2Name AS Actor2Name,
  GDELT.AvgTone AS AvgTone,
  GDELT.SOURCEURL AS SOURCEURL
FROM
  w205_final_project.blockchain_data AS blockchain
INNER JOIN
  w205_final_project.GDELT AS GDELT
ON
  blockchain.timestamp = GDELT.SQLDATE

Return the following :

Error: No matching signature for operator = for argument types: TIMESTAMP, INT64. Supported signature: ANY = ANY at [18:3]

GDELT.SQLDATE column data is in integer format to begin with, as YYYYMMDD; blockchain.timestamp column is in timestamp format to begin with, for example:

2015-01-01 04:29:21 UTC

Upvotes: 1

Views: 177

Answers (1)

Blag
Blag

Reputation: 5894

I'm not a BigQuery expert, but SQLDAT(_PARSED) is a field only generated AFTER the join in this query, you'll not find it as SQLDATE(_PARSED) in your join clause

You should switch your GDELT.SQLDATE to PARSE_TIMESTAMP('%Y%m%d', CAST(GDELT.SQLDATE AS STRING))

#standardSQL
SELECT
  blockchain.num_tx AS `num_tx`,
  blockchain.timestamp AS `timestamp`,
  GDELT.GLOBALEVENTID AS GLOBALEVENTID,
  PARSE_TIMESTAMP('%Y%m%d', CAST(SQLDATE AS STRING)) AS SQLDATE,
  GDELT.Actor1Code AS Actor1Code,
  GDELT.Actor1Name AS Actor1Name,
  GDELT.Actor2Code AS Actor2Code,
  GDELT.Actor2Name AS Actor2Name,
  GDELT.AvgTone AS AvgTone,
  GDELT.SOURCEURL AS SOURCEURL
FROM
  final_project.blockchain_data AS blockchain
INNER JOIN
  final_project.GDELT AS GDELT
ON
  blockchain.timestamp = PARSE_TIMESTAMP('%Y%m%d', CAST(GDELT.SQLDATE AS STRING))

Second point, don't name your column timestamp, it's a SQL keyword, it's reserved, same go for time, date, ... At least, try to rename your alias to something like blockchain.timestamp AScreated`

And avoid naming your alias the same than your column :

SELECT PARSE_TIMESTAMP('%Y%m%d', CAST(GDELT.SQLDATE AS STRING)) AS SQLDATE,
FROM blockchain_data AS blockchain
INNER JOIN GDELT 
    ON blockchain.timestamp = GDELT.SQLDATE;

Is not the same as :

SELECT PARSE_TIMESTAMP('%Y%m%d', CAST(SQLDATE AS STRING)) AS SQLDATE_PARSED,
FROM blockchain_data AS blockchain
INNER JOIN GDELT 
    -- not what you want, but what you do, with error TIMESTAMP = INT64
    ON blockchain.timestamp = SQLDATE 
    -- what you want, but show ERROR column not fould
    ON blockchain.timestamp = SQLDATE_PARSED 
    -- Is the valid expression  
    ON blockchain.timestamp = PARSE_TIMESTAMP('%Y%m%d', CAST(SQLDATE AS STRING))

Upvotes: 2

Related Questions