Your AI Companion in the Google Cloud

In today's data-driven world, staying competitive means harnessing the power of artificial intelligence. Google Cloud offers a remarkable tool called Duet AI, and it's revolutionising the way we analyze data. Duet AI, designed for BigQuery, is a game-changer for anyone working with data. It's a valuable assistant for data analysts, scientists, and developers who use SQL queries. Whether you are starting a new query, completing an existing one, or tackling a complex query, Duet AI serves as your intelligent companion in the world of BigQuery.

Let's see how Duet AI is changing the world of data analysis by making complicated tasks easier and helping businesses find important information faster.

Duet AI is a smart friend that helps people in three main ways:

1. Query Generation: Duet AI can generate SQL queries based on natural language prompts. It suggests SQL statements, even if you're starting with minimal knowledge of data schema or Google SQL syntax.

2. Query Completion: As you type, Duet AI offers contextually relevant recommendations, enhancing query accuracy, and expediting the query writing process.

3. Query Explanation: Duet AI can provide explanations for complex SQL queries in natural language, helping users grasp the underlying schema and business context, especially in the case of lengthy or intricate queries.

Getting started

Prior to using Duet AI in BigQuery, it is essential for you to be familiar with querying data in BigQuery. Following are the steps you should take:

1. Project Setup

Ensure that you have set up Duet AI for your Google Cloud user account and project. Setting up Duet AI for your Google Cloud project involves a few straightforward steps. 

• First, apply for the Duet AI preview by filling out a form
• Once accepted, you'll be instructed to submit the project IDs you want to use with Duet AI
• After confirmation from Google, enable the Cloud AI Companion API for your Google Cloud project, but only for projects submitted after being accepted in the preview

After this, Duet AI becomes accessible in all supported Google Cloud products within that project. It's important to note that you can't selectively enable or disable it for specific products simultaneously. Depending on the product, you may also need to activate specific Duet AI features, so it's a good idea to consult the product documentation for more information.

2. Enable Duet AI  Features 

In the BigQuery interface, you can enable or disable specific Duet AI features, such as Auto-completion, Auto-generation and Explanation. These features can be tailored to your specific needs.

Generating SQL queries with Duet AI

Creating SQL queries using Duet AI is easy. You don't need any prior coding experience, extensive knowledge of data structures, or a deep understanding of SQL syntax. Duet AI can help you, and here's how you can begin:

1. Go to the SQL query editor in BigQuery.
2. Begin your SQL request by using the '#' symbol.
3. After the '#', write a sentence or question in plain language about the data you need.
4. Duet AI will give you SQL suggestions to help you analyse your data, based on what you wrote. You can make your questions or requests longer by typing them on multiple lines in the query editor. Just remember to start each new line with a # character.

For example, if you want to figure out which bike stations are used the most, you can do that by looking at where the bike trips begin in the San Francisco bike share dataset. Here's how you can do it:

Duet AI will then suggest SQL queries similar to the one above, allowing you to explore your data effortlessly. When you hover your mouse over the suggested query, you can choose to switch to other suggested queries. You can agree with the suggested search by either pressing the Tab key or clicking on the "Accept" button.

Just like when talking to a person, it's important to be clear when you're asking Duet AI for help. The clearer your questions or instructions, the better Duet AI can assist you. The quality of the prompt plays a crucial role in getting the desired results from AI. When prompts are too generic or vague, Duet AI may generate queries or responses that don't align with your actual intent or requirements. To improve Duet AI interactions, it's essential to provide specific and detailed prompts that clearly convey what you want to achieve or ask. This helps ensure that Duet AI understands and produces relevant and accurate outputs. As demonstrated below, the prompt we used is too general, resulting in a suggested query that doesn't meet our specific requirements.

Let's now try another prompt.

This prompt is clear and well-structured. It tells Duet AI exactly what it needs to do, like which tables and columns to use, what type of calculation to do, and which dataset to work with. It also mentions how to connect the data using the order ID, giving all the details needed to create a useful query. As a result, it does well in achieving the outcome you want.

Completing SQL Queries with Duet AI

Using Duet AI for SQL completion is meant to give you useful suggestions while you write your SQL query. This can be really handy when you are working on your query. Here's how to use it:

1. Begin typing your SQL query in the BigQuery query editor.

2. To make SQL suggestions appear, just press Enter or Space. Duet AI will give you suggestions that make sense for your query, which can help you make your query better as you go along.

For instance, in the example below, when you see the message that says "Syntax error: Unexpected end of script at [11:27]," it means that your query isn't finished yet. To continue and get help with calculating sales revenue, you can press the 'Enter' key, and Duet AI will provide suggestions.

3. To agree with the idea given, do this: press the Tab key, or move your mouse pointer over the suggested text and click on it if you see other options. If you want to ignore the suggestion, press the ESC key or just keep typing as usual.

Explaining SQL queries with Duet AI

Sometimes, figuring out a complicated SQL query can be hard, especially if it's long or tricky. Duet AI can help by explaining it in plain language. Here's how to use this feature:

1. Open the SQL query you want to understand in the BigQuery editor.

2. Select the query.

3. Click on the "Explain this query" option found on the left side of your query. It looks like a small pencil in the Duet AI panel.

Now, let's ask Duet AI to explain the query it helps us finish.

Here is the response:

Conclusion

In the fast-changing world of data analysis, having a tool like Duet AI in Google Cloud is a big advantage. It makes complicated coding tasks easier, offers smart suggestions, and makes data analysis work more efficiently. With Duet AI, businesses can use their data better, discover deeper insights, and make decisions based on data that lead to success.

As we move into this new age of digital transformation, powered by generative AI, it's clear that tools like Duet AI will keep changing how we do data analysis. There are endless possibilities, and companies that use this technology will be leaders in innovation and competitiveness.

However, it's important to note that Duet AI, as early-stage technology, might sometimes provide answers that seem right but are actually wrong. So, it's a good idea to double-check any information you get from Duet AI before using it in your work.

Further Links

Check out our LinkedIn account, to get insights into our daily working life and get important updates about BigQuery, Data Studio, and marketing analytics

We also started with our own YouTube channel. We talk about important DWH, BigQuery, Data Studio, and many more topics. Check out the channel here.

If you want to learn more about using Google Data Studio and taking it to the next level in combination with BigQuery, check out our Udemy course here.

If you are looking for help setting up a modern and cost-efficient data warehouse or analytical dashboard, email us at hello@datadice.io and we will schedule a call.

‍