Create daily bigquery and append to table

[guil-lambert]

No description provided.

[guil-lambert]

I created a scheduled query that appends the last day's data to a table hosted here: arcane-world-371019.First_sync.1

Here's the full query string:

SELECT
  address,
  block_number,
  transaction_hash,
  block_timestamp,
  log_index,
  transaction_index,
  CONCAT('0x',(CAST(SUBSTRING(DATA, 3, 64) AS string))) AS amount0,
  CONCAT('0x',(CAST(SUBSTRING(DATA, 67, 64) AS string))) AS amount1,
  CONCAT('0x',(CAST(SUBSTRING(DATA, 131, 64) AS string))) AS sqrtPrice,
  CONCAT('0x',(CAST(SUBSTRING(DATA, 195, 64) AS string))) AS liquidity,
IF
  ((CAST(CONCAT('0x',(CAST(SUBSTRING(DATA, 317, 16) AS string))) AS int)) > CAST('0xf00000' AS int), (CAST(CONCAT('0x',(CAST(SUBSTRING(DATA, 317, 16) AS string))) AS int))- CAST('0xffffff' AS int), CAST(CONCAT('0x',(CAST(SUBSTRING(DATA, 317, 16) AS string))) AS int)) AS tick
FROM
  `bigquery-public-data.crypto_ethereum.logs`
WHERE
  DATA != ""
  AND transaction_hash != ""
  AND ARRAY_LENGTH(topics) != 0
  AND topics[
OFFSET
  (0)] = "0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67"
  AND (CAST(SUBSTRING(DATA, 259, 64) AS string)) != ''
  AND DATE(block_timestamp) >= "2021-05-01"
ORDER BY
  block_timestamp ASC,
  transaction_index

[guil-lambert]

I am able to access the database using the following query:

pandas.read_gbq("SELECT * FROM `arcane-world-371019.First_sync.1` LIMIT 1000", "arcane-world-371019")

Juan can't:

hmmm I keep getting access denied (both with my gmail and panoptic accounts)
GenericGBQException: Reason: 403 POST https://bigquery.googleapis.com/bigquery/v2/projects/arcane-world-371019/jobs?prettyPrint=false: Access Denied: Project arcane-world-371019: User does not have bigquery.jobs.create permission in project arcane-world-371019.


Location: None
Job ID: 50bb0cfb-8ac4-4b82-b008-c90e8ce81897

I added him directly as a principal, we'll see if that fixes it.

[guil-lambert]

Scheduled! Runs every day at 11:59pm UTC