Assertions prevent using joindiff on bigquery tables in different projects, even though it works fine.

[segv]

Request: If I explicitly call data_diff.diff_tables and pass in algorithm=data_diff.Algorithm.JOINDIFF it'd be wonderful if the tool would let me use it, even if it thinks I'm applying to two different DBs (maybe just a warning message that I can ignore?)

Context:

In bigquery you can always (permissions aside) join tables in different datasets and projects, however since data_diff considers two different projects to be two differnt databases a few of the assertions in JoinDiff fail.

Unlike in snowflake or postgres DBs, where prod and dev would be different schemas in the same DB, when using BigQuery I've found it to be pretty common to have a prod project and then each dev gets their own playground project.

I went in and commented out three assertions (and had to change the default value of SegmentInfo.rowcounts to {1: 0, 2: 0}, but once that was done the code worked great and was substantially faster than the hash diff.

[erezsh]

Hi @segv, thanks for bringing it to our attention.

It sounds like the best approach would be for data-diff to test the equality of BigQuery connections without comparing datasets or projects. Then there would be no need to manually override the tool.

Does that sound right?

[segv]

test the equality of BigQuery connections

I do not know enough about how the connections are implemented to answer this.

fwiw the code I was using was this:

        dev = data_diff.connect_to_table(f"bigquery://{project}/{dataset}", table, TABLE_IDS[table])
        prd = data_diff.connect_to_table(f"bigquery://prod/{dataset}", table, TABLE_IDS[table])

If connect_to_table will avoid creating a new connection for that second call it'd be fine. Otherwise I'd have to construct the TableSegment myself, correct? This isn't bad, and if it works that's fine, just trying to understand

[erezsh]

I see three options, I don't know which is better yet, I'll have to look into it.

1. The second connect_to_table() re-uses the same connection, despite having different arguments
2. The second connect_to_table() creates a new connection, but data-diff can detect they are on the same database.
3. Use the exact same connection string, and instead specify the different dataset and project in the table path.

I think (3) is the easiest to implement.

[segv]

I'm am totally fine with (3) (it kind of fits google's model where every query and api call has a "home" project for billing/auditing purposes). However I did not see a way to specify the project/dataset when creating a TableSegment. What I'd like to write is something along the lines of:

prd = data_diff.connect_to_table(f"bigquery://segv-project/dev", "`production-project`.table", "id")

does that make sense? (and given that table names can be weird, it's probably impossible to parse out a project id from a table name, but I could be wrong)

[erezsh]

Well, right now both of these forms work:

prd = data_diff.connect_to_table(f"bigquery://segv-project/dev", "schema.table", "id")

prd = data_diff.connect_to_table(f"bigquery://segv-project/dev", ("schema", "table"), "id")

Where the second form should be able to handle any weirdness in the names. (it quotes them automatically when generating the SQL)

But right now it only accepts a different schema, so I'll have to add the option for specifying a different project too. Sounds like a good plan?

[segv]

But right now it only accepts a different schema, so I'll have to add the option for specifying a different project too. Sounds like a good plan?

Yes.

If you can point me in basically the right direction I can coming up with a PR myself

[erezsh]

I don't think you need to change much outside of bigquery.py: https://github.com/datafold/data-diff/blob/master/data_diff/sqeleton/databases/bigquery.py

Specifically, you'll have to override _normalize_table_path() (see base.py for the basic structure)

[erezsh]

@segv This has just been implemented in Snowflake: #335

It's probably a very similar implementation for BigQuery.

[segv]

So I did what #335 did, but that wasn't all I had to do. The PR works for me but I'm more than happy to adjust it (I didn't test beyond my own specific use case)

#348

[erezsh]

Replied there.

[github-actions]

This issue has been marked as stale because it has been open for 60 days with no activity. If you would like the issue to remain open, please comment on the issue and it will be added to the triage queue. Otherwise, it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment and it will be reopened for triage.