feat: implement backend compatible with Google Bigquery

andyquinterom commented 1 year ago

PR Info

This PR implements a backend for the popular SQL datalake solution, Google BigQuery. We need this in our company to be able to query BigQuery from our Rust projects.

This is working quite well but feedback is welcome to be able to merge this upstream. This is something we really need at our company and having this upstreamed would make things much easier.

New Features

Implements BigQuery backend for Sea-Query behind the backend-bigquery feature flag.

Details

Implements all basic SQL types and their equivalent BigQuery version (https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types).
NUMERIC or BIGNUMERIC are chosen based on the user's specified scale and precision.

billy1624 commented 1 year ago

Hey @andyquinterom, thanks for the PR!! It's great to see BigQuery user here! Welcome :P

One question. How do you execute the constructed query in Rust? What's the driver in use?

andyquinterom commented 1 year ago

Hey @andyquinterom, thanks for the PR!! It's great to see BigQuery user here! Welcome :P

One question. How do you execute the constructed query in Rust? What's the driver in use?

Here is a working example:

Cargo.toml

[package]
name = "rust_bigquery"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
gcp-bigquery-client = "0.16.7"
tokio = { version = "1.28.2", features = ["full"] }
sea-query = { git = "https://github.com/andyquinterom/sea-query.git", branch = "bigquery", features = ["backend-bigquery"] }
anyhow = "1.0.71"

main.rs

use anyhow::Result;
use gcp_bigquery_client::model::query_request::QueryRequest;
use sea_query::{BigQueryQueryBuilder, Iden, SelectStatement};

struct Project;

impl Iden for Project {
    fn unquoted(&self, s: &mut dyn std::fmt::Write) {
        write!(s, "my-bigquery-project").expect("Count not write unquoted project")
    }
}

struct Dataset;

impl Iden for Dataset {
    fn unquoted(&self, s: &mut dyn std::fmt::Write) {
        write!(s, "rust").expect("Count not write unquoted project")
    }
}

#[derive(Iden)]
enum Iris {
    Table,
    SepalLength,
    SepalWidth,
    PetalLength,
    PetalWidth,
    Variety,
}

#[tokio::main]
async fn main() -> Result<()> {
    let client = gcp_bigquery_client::Client::from_service_account_key_file("sa.json").await?;

    let query = SelectStatement::new()
        .from((Project, Dataset, Iris::Table))
        .columns([Iris::Variety])
        .distinct()
        .to_string(BigQueryQueryBuilder);

    let mut result = client
        .job()
        .query(&Project.to_string(), QueryRequest::new(query))
        .await
        .unwrap();

    while result.next_row() {
        println!("{:?}", result.get_string_by_name("variety"));
    }

    Ok(())
}

magbak commented 1 year ago

Hi! Bigquery support would be really good. I was not aware of this work, but have done a little bit of work to support my Bigquery use case here: https://github.com/DataTreehouse/sea-query/tree/feature/bigquery_basic_support Specifically I needed to use a subquery with UNNEST([STRUCT...) as well as some datetime functions. Would be happy to help integrating the changes.

SeaQL / sea-query