i have questions about Exceeded rate limits issue in Google Big Query. I need to compare two tables (about 30k rows) and find unique people in first table and find unique people in the other one. I need to insert these "new" people into another tables and get an Exceeded rate limits issue. I use Python to make queries to GBQ and even create a class for it.

1. Is it possible to find people without using Python list comparing - just in GBQ?

2. Is it possible to remove Exceeded rate limits issue using my Python class?

3. Maybe, is it better to use built-in functions like here: https://cloud.google.com/bigquery/docs/streaming-data-into-bigquery

My class has a lot of functions (select, insert, delete, etc.) and looks like this:

class GBQ_Class():
    def __init__(self, project, dataset, table):
        self.project = project
        self.dataset = dataset
        self.table = table
        self.client = bigquery.Client()

    def select(self, limit):
        query = """
            SELECT * FROM `{0}.{1}.{2}` LIMIT {3}
        """.format(self.project, self.dataset, self.table, limit)
        query_job = self.client.query(query)
        return query_job

CodePudding user response：

For your first question, yes, it is possible by making use of the compare query that I am sharing below:

#standardSQL 
SELECT IFNULL(table1.key1, table2.key1) key1,
IFNULL(table1.key2, table2.key2) key2 
FROM `table1` AS table1 
FULL OUTER JOIN `table2` AS table2 
ON table1.key1 = table2.key1 
AND table1.key2 = table2.key2 
WHERE TO_JSON_STRING((table1.column1, table1.col2)) != TO_JSON_STRING((table2.column1, table2.col2))

For your second question, specifically for the code you are sharing you can’t remove the exceeded rate limit since you are reaching the table update limit. This happens because the quota replenishes periodically, you would have to wait a couple minutes to try again, but take care of your table update quota so you don’t receive this error again.

And for your third point, in Stack Overflow it isn’t allowed to ask for recommendations since this would change to an opinion based question and your question could be closed, you can read more about these best practices here.