Getting started

Security and Performance

• Security: In its current form, the proxy does nothing more than rewrite the SQL text on behalf of the user and forward the payload to the BigQuery API with the same credentials as the user. The proxy does not necessitate or store any credentials to operate. Any user calling the proxy API will appear to the BigQuery API as exactly the same user with exactly the same permissions as if the user was calling the BigQuery API directly. No data from BigQuery results are ever exposed beyond the proxy boundary. Outside of this the proxy adheres to Alvin’s robust security procedures that follows from Alvin internal policies as well as SOC2 Type2 certification.

• Performance: The Query Optimizer is implemented in rust/actix, a language and framework known for extreme performance and reliability. The Query Optimizer only adds single-digit milliseconds to the response time of any request, that will be unnoticeable.

• Reliability: As stated already, the Query Optimizer is built from the ground up to handle large request volumes, fast. In addition to this, there are tests associated with every rewrite rule and these are run on every commit and prior to any deployment. If, by any chance, there is any error in processing and rewriting any SQL payload, the proxy will always fall back to passing on the original, untouched API request and payload, ensuring no disruption of service.

• Accuracy: Lastly, it’s important to note that the Proxy is not using LLMs/GenAI or any approach that is potentially non-deterministic, nor sending SQL outside of the proxy. It is purely based on generating an AST and replacing nodes/expressions with a functionally equivalent expression that will return the same value. The proxy works in a binary way, either it rewrites the query to an optimized, but equivalent functional form, or it does not - it would never return a query that would yield a different result. For every query rewrite, the Query Optimizer also maintains a log of what rules/transformations were applied to the SQL.

Rollout Approach:

In this section we will describe a few different scenarios for rolling out the Query Optimizer, depending on the current setup/configuration of the Looker/BigQuery environment. The proxy URL will be given to you by an Alvin representative - reach out at [e-mail] if you are interested in trying it out.

• Full rollout: For simple BI environments, we would recommend just “flipping the switch” in terms of routing the traffic via the Query Optimizer. This can be easily done in the Looker connection setup, by setting the rootUrl jdbc parameter. See below for more details on this. This means that all users in the Looker instance would immediately have their queries passed through and optimized by the Query Optimizer.

• Gradual rollout to users/groups: It can also be that the BigQuery/Looker environment is more complex, and a more fine-tuned approach is desired. A complex environment can be where users are doing a lot of direct querying via SQL Runner / Explores and there might be more realtime data sources. This gradual rollout approach is described below. Here the proxy can be enabled on an individual user/group basis, allowing you to quickly validate the performance and results of the proxy before rolling out to the entire company.

As a note, it is not possible to change the connection of an existing dashboard. To test on a single dashboard, you would need to duplicate the dashboard, along with any models and explores that the dashboard references - and ensure that all models use the the Query Optimizer as the connection.

Full rollout

Simply set the rootUrl jdbc parameter as described in the documentation. This can be done by navigating to the admin panel like this: https://<your-looker-instance>/admin/next/connections/<connection-id>/edit : The value to set is rootUrl=<proxy_url> . The URL will be shared with you by an Alvin representative. Make sure that the URL has the https protocol and that it looks something like this: https://proxy.bigquery.com and not just proxy.bigquery.com .

Gradual rollout to users/groups

Looking at the documentation, it is possible to have a user attribute defining properties and parameters of a connection, and this is exactly what we will do with the rootUrl parameter similarly as above. As we can see in the below screenshots, the connection’s jdbc parameters is set up using liquid templating syntax instead being hard coded. Follow the instructions below to set it up:

1. Go to https://<your-looker-instance>.cloud.looker.com/admin/user_attributes and create a new user attribute named alv_proxy_url. The default value of this attribute should be the https://bigquery.googleapis.com, which is the base URL of the BigQuery API as per the documentation at https://cloud.google.com/bigquery/docs/reference/rest.

   
2. From the user attribute list, select a user or group of your choosing and set the proxy URL. Your proxy URL will be shared with you with a Alvin representative (if you are interested please reach out on [e-mail]). Make sure that the URL has the https protocol and that it looks something like this: https://proxy.bigquery.com and not just proxy.bigquery.com . For more insight in how to set up users and groups, check out the official documentation: https://cloud.google.com/looker/docs/admin-panel-users-groups. Once you have the appropriate groups set up, look them up and set the value as shown in the screenshot.

1. Go to the connection setup at https://<your-looker-instance>/admin/next/connections/<connection-id>/edit and set the Additional JDCB Parameters field like this: rootUrl={{ _user_attributes['alv_proxy_url'] }}

1. You're good to go!