How can i do a query using a database and a Gsheet information

Queries and Resources
,
1

Hi guys:
I have this "Query JSON with SQL" that bring 3 columns from a GSheet (Tax_id, owner, customer).

image
image854×674 38.4 KB

I would like to do a query using a snowflake DB and bring the owner for each customer using the TAX_ID as the key between snowflake and Gsheet . Does anybody knows how can i do this?

2

Hey @AndrewG,

Have you created your snowflake query? This should be straightforward. Use an alias on your js-invoked tables so you can access your columns accurately. In your screenshot, you can do something like:

SELECT
  pg.tax_id,
  pg.owner,
  pg.customer,
  sq.created_at,
  sq.column7,
  sq.etc
FROM {{Prueba_Gsheet.data}} [AS] pg
JOIN {{snowflake_query.data}} [AS] sq ON sq.tax_id = pg.tax_id

Note: words inside [] are optional

3

Hi jocen, Thanks,
the query run perfectly with your advice. But i have another question, this kind of querys have to be runed in the "Query JSON with SQL" resource?
Because i would like the result to be transposed. look an image of the result:

image
image1350×1094 76.2 KB

4

Hi @AndrewG,

You probably want to use the transformer for this and do something like:

return formatDataAsObject(data)

but not sure with how you queried this or what was your sql look like.