CODEWITHSUNDEEP
google-bigquery
Aman Mathur
Aman Mathur

Reputation: 719

Finding the query that created a table in BigQuery

I am a new employee at the company. The person before me had built some tables in BigQuery. I want to investigate the create table query for that particular table.

Things I would want to check using the query is:

  1. What joins were used?
  2. What are the other tables used to make the table in question?

I have not worked with BigQuery before but I did my due diligence by reading tutorials and the documentation. I could not find anything related there.

Upvotes: 4

Views: 11538

Answers (3)

Qetroux
Qetroux

Reputation: 1

For me, I was able to go through my query history and find the query I used.

Step 1.

Go to the Bigquery UI, on the bottom there are personal history and project history tabs. If you can use the same account used to execute the query I recommend personal history.

Step 2.

Click on the tab and there will be a list of queries ordered from most recently run. Check the time the table was created and find a query that ran before the table creation time.

Since the query will run first and create the table there will be slight differences. For me it stayed between a few seconds.

Step 3.

After you find the query used to create the table, simply copy it. And you're done.

Upvotes: 0

user18568350
user18568350

Reputation: 1

I have been looking for an answer since a long time. Finally found it : Go to the three bars tab on the left hand side top

From there go to the Analytics tab.

Select BigQuery under which you will find Scheduled queries option,click on that.

In the filter tab you can enter the keywords and get the required query of the table.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

Brief outline of your actions below:

Step 1 - gather all query jobs of that user using Jobs.list API - you must have Is Owner permission for respective projects to get someone else's jobs

Step 2 - extract only those jobs run by the user you mentioned and referencing your table of interest - using destination table attribute

Step 3 - for those extracted jobs - just simply check respective queries which allow you to learn how that table was populated

Hth!

Upvotes: 5

Related Questions