Add Support for Google BigQuery as a Database Dialect

[dedalo95]

I am currently using Goose to manage migrations for PostgreSQL databases and have found it to be an excellent tool. I would like to extend its use to manage migrations for Google BigQuery datasets. However, I noticed that BigQuery is not currently supported as a database dialect in Goose.

I am aware that Goose allows for the implementation of custom stores through the goose.NewProvider function, as discussed in Issue #742. While this provides a workaround, having native support for BigQuery would streamline the migration process and benefit other users who utilize BigQuery.

Request:

I am requesting the addition of native support for Google BigQuery as a database dialect in Goose. This would involve implementing a BigQuery-compatible dialect that can handle the specific SQL syntax and features of BigQuery.

Benefits:

Consistency: Users can manage migrations across different database systems using a single tool.
Efficiency: Eliminates the need for custom implementations, reducing development time and potential errors.
Community Support: Encourages collaboration and shared solutions for BigQuery migrations within the Goose community.

[colinmf]

Much needed schema versioning for bigquery.

[mfridman]

Fwiw we don't mind adding native dialect support. It gets tricky when there are dozens of configurations per dialect, such as ClickHouse.

Adding a dialect typically means adding the raw SQL, exposing a constant and adding a test.

Maybe this project could be used to fulfill the integration tests: https://github.com/goccy/bigquery-emulator