Vector search looks for similar vectors or data points in a data set based on their vector representations. Unlike proprietary vector databases such as Pinecone, Milvus, Qdrant and Weaviate, MyScale is built on the open source, SQL-compatible ClickHouse database.

SQL is an effective tool for managing relational databases. Combining SQL and vectors provides a powerful approach to tackling complex AI-related questions. Users can execute traditional SQL and vector queries on structured data and vector embeddings (data) to address complex queries and analyze high-dimensional data in a unified, efficient manner.

Advanced SQL Techniques for Complex Queries

Simple SQL queries are commands that perform straightforward data retrieval, usually from only one table at a time. Complex SQL queries go beyond standard requests by retrieving data from several tables and limiting the result set with multiple conditions.

A complex query could include features such as:

• Common table expressions
• Subqueries
• Joining to many tables and using different join types

Common Table Expressions

A common table expression (CTE) is a name you give a subquery within your main query. The main reason to do this is to simplify your query, making it easier to read and debug. It can sometimes improve performance, which is another benefit, but it’s mostly about readability and simplification.

Consider a scenario in which you want to determine the average age of customers who bought a particular product. You have a table of customer data, including their name, age and the products they purchased.

Here’s an example query to perform this calculation using a CTE:

This CTE — a temporary named result set (subquery) that can be referenced within a single query — is named product_customers. It’s created using a SELECT statement that retrieves the name and age columns from the customer_data table for customers who purchased the product 'widget'.

Moving the subquery to the top of the query and giving it a name makes it easier to understand what the query does. If your subquery selects a sample embedding vector, you could name your subquery something like target_vector_embed. When you refer to this in the main query, you’ll see this name and know what it refers to.

This is also helpful if you have a long query and need the same logic in several places. You can define it at the top of the query and refer to it multiple times throughout your main query.

So consider using CTEs when having a subquery improves the readability of your query.

Subqueries

A subquery is a simple SQL command embedded within another query. By nesting queries, you can set up larger restrictions on the data included in the result set.

Subqueries can be used in several places within a query, but it’s easiest to start with the FROM statement. Here’s an example of a basic subquery:

I’ll break down what happens when you run the above query:

First, the database runs the “inner query” — the part between the parentheses. If you run this independently, it produces a result set just like any other query. However, after the inner query runs, the outer query runs using the results from the inner query as its underlying table:

Subqueries must have names, which are added after parentheses (the same way you would add an alias to a regular table). This query uses the name sub.

Using Subqueries in Conditional Logic

You can use subqueries in conditional logic (in conjunction with WHERE, JOIN/ON or CASE). The following query returns all the entries from the same date as the specified entry in the data set:

This query works because the result of the subquery is only one cell. Most conditional logic will work with subqueries containing one-cell results. However, IN is the only type of conditional logic that will work when the inner query contains multiple results:

Note that you should not include an alias when you write a subquery in a conditional statement. This is because the subquery is treated as an individual value (or set of values in the IN clause) rather than as a table.

Joining Tables

join produces a new table by combining columns from one or multiple tables by using values common to each. Different types of joins are:

• INNER JOIN: Only matching rows are returned.
• LEFT JOIN: Nonmatching rows from the left table and matching rows are returned.
• RIGHT JOIN: Nonmatching rows from the right table and matching rows are returned.
• FULL JOIN: Nonmatching rows from both tables and matching rows are returned.
• CROSS JOIN: Produces the Cartesian product of whole tables, as “join keys” are not specified.

Using Complex SQL and Vector Queries in MyScale

SQL vector database MyScale includes several features that help with complex SQL and vector queries.

Common Table Expressions

MyScale supports CTE and substitutes the code defined in the WITH clause for the rest of the SELECT query. Named subqueries can be included in the current and child query context anywhere table objects are allowed.

Vector search is a search method that represents data as vectors. It is commonly used in applications such as image, video and text search. MyScale uses the distance() function to perform vector searches. It calculates the distance between a specified vector and all vector data in a specified column and returns the top candidates.

In some cases, if the specified vector is obtained from another table or the dimension of the specified vector is large and inconvenient to represent, you can use CTE or subquery.

Assume you have a vector table named photos that stores metadata information linked to your photo library’s images, with id, photo_id and photo_embed for the embedding vector.

The following example treats the result of a selection in CTE as a target vector to execute vector search:

Joins and Subqueries

There is limited support in MyScale for join, and using subquery is recommended as a workaround. In MyScale, the vector search is based on the vector index on a table with a vector column. Although the distance() function appears in the SELECT clause, its value is calculated during vector search on the table, not after join. The join result may not be the expected result.

The following are possible workarounds:

• You can use the distance()...WHERE...ORDER BY...LIMIT query pattern in subqueries that utilize vector indexes and get expected results on vector tables.
• You can also use subqueries in the WHERE clause to rewrite the join.

Assume you have another table, photo_meta, that stores information about the photo library’s images with photo_id, photo_author, year and title. The following example retrieves relevant photos taken in 2023 from a collection of images:

Here’s what happens when you run the above query:

First, MyScale executes vector search on the table photos to get the required column photo_id and the value of the distance() function for the top five relevant records:

Then, the join runs using the results from the vector table as its underlying table:

Because the vector search doesn’t consider the year photos were taken, the result may be incorrect. To get the correct result, rewrite the join query by using a subquery:

Improve Data Analysis

Advanced SQL techniques like CTEs, subqueries and joins can help you perform complex data analyses and manipulations with greater precision and efficiency. MyScale combines the power of SQL and vectors to provide a powerful approach to tackling complex AI-related questions. With MyScale, you can efficiently execute traditional SQL and vector queries on structured and vector data to address complex queries and analyze high-dimensional data in a unified and efficient manner.

If you are interested in learning more, please follow us on X (Twitter) or join our Discord community. Let’s build the future of data and AI together!

The post How To Run Complex Queries With SQL in Vector Databases appeared first on The New Stack.

Members of the research community at Microsoft work continuously to advance their respective fields. Abstracts brings its audience to the cutting edge with them through short, compelling conversations about new and noteworthy achievements.

In this episode, Senior Principal Researcher Michel Galley joins host Gretchen Huizinga to discuss “MathVista: Evaluating Mathematical Reasoning of Foundation Models in Visual Contexts,” which was accepted at the 2024 International Conference on Learning Representations (ICLR). MathVista, an open-source benchmark, combines new and existing data to measure how good models are at solving a variety of math problems that involve processing images as well as text, helping to gain insight into their reasoning capabilities.

The post Abstracts: May 6, 2024 appeared first on Microsoft Research.

DevSecOps teams can sometimes find they need to run an instance of Visual Studio Code (VS Code) remotely for team members to share when they don't have enough local resources. However, installing, running, and using VS Code on a remote virtual machine (VM) via a cloud provider can be a complex process full of pitfalls and false starts. This tutorial covers how to automate the installation of VS Code on a VM running on a cloud provider.

This approach involves two separate GitLab projects, each with its own pipeline. The first one uses Terraform to instantiate a virtual machine in GCP running Linux Debian. The second one installs VS Code on the newly instantiated VM. Lastly, we provide a procedure on how to set up your local Mac laptop to connect and use the VS Code instance installed on the remote VM.

Create a Debian Linux distribution VM on GCP

Here are the steps to create a Debian Linux distribution VM on GCP.

Prerequisites

1. A GCP account. If you don't have one, please create one.
2. A GitLab account on gitlab.com

Note: This installation uses:

• Debian 5.10.205-2 (2023-12-31) x86_64 GNU/Linux, a.k.a Debian 11

Create a service account and download its key

Before you create the first GitLab project, you need to create a service account in GCP and then generate and download a key. You will need this key so that your GitLab pipelines can communicate to GCP and the GitLab API.

1. To authenticate GCP with GitLab, sign in to your GCP account and create a GCP service account with the following roles:

• Compute Network Admin
• Compute Admin
• Service Account User
• Service Account Admin
• Security Admin

3. Download the JSON file with the service account key you created in the previous step.

4. On your computer, encode the JSON file to base64 (replace /path/to/sa-key.json to the path where your key is located):

   base64 -i /path/to/sa-key.json | tr -d \\n
   

NOTE: Save the output of this command. You will use it later as the value for the BASE64_GOOGLE_CREDENTIALS environment variable.

Configure your GitLab project

Next, you need to create and configure the first GitLab project.

1. Create a group in your GitLab workspace and name it gcpvmlinuxvscode.

2. Inside your newly created group, clone the following project:

   git@gitlab.com:tech-marketing/sandbox/gcpvmlinuxvscode/gcpvmlnxsetup.git
   

3. Drill into your newly cloned project, gcpvmlnxsetup, and set up the following CI/CD variables to configure it:

   1. On the left sidebar, select Settings > CI/CD.
   2. Expand Variables.
   3. Set the variable BASE64_GOOGLE_CREDENTIALS to the base64 encoded JSON file you created in the previous section.
   4. Set the variable TF_VAR_gcp_project to your GCP project ID.
   5. Set the variable TF_VAR_gcp_region to your GCP region ID, e.g. us-east1, which is also its default value.
   6. Set the variable TF_VAR_gcp_zone to your GCP zone ID, e.g. us-east1-d, which is also its default value.
   7. Set the variable TF_VAR_machine_type to the GCP machine type ID, e.g. e2-standard-2, which is also its default value.
   8. Set the variable TF_VAR_gcp_vmname to the GCP vm name you want to give the VM, e.g. my-test-vm, which is also its default value.

Note: We have followed a minimalist approach to set up this VM. If you would like to customize the VM further, please refer to the Google Terraform provider and the Google Compute Instance Terraform provider documentation for additional resource options.

Provision your VM

After configuring your project, manually trigger the provisioning of your VM as follows:

1. On the left sidebar, go to Build > Pipelines.
2. Next to Play ({play}), select the dropdown list icon ({chevron-lg-down}).
3. Select Deploy to manually trigger the deployment job.

When the pipeline finishes successfully, you can see your new VM on GCP:

• Check it on your GCP console's VM instances list.

Remove the VM

Important note: Only run the cleanup job when you no longer need the GCP VM and/or the VS Code that you installed in it.

A manual cleanup job is included in your pipeline by default. To remove all created resources:

1. On the left sidebar, select Build > Pipelines and select the most recent pipeline.
2. For the destroy job, select Play ({play}).

Install and set up VS Code on a GCP VM

Perform the steps in this section only after you have successfully finished the previous sections above. In this section, you will create the second GitLab project that will install VS Code and its dependencies on the running VM on GCP.

Prerequisites

1. A provisioned GCP VM. We covered this in the previous sections.

Note: This installation uses:

• VS Code Version 1.85.2

Configure your project

Note: Since you will be using the ssh command multiple times on your laptop, we strongly suggest that you make a backup copy of your laptop local directory $HOME/.ssh before continuing.

Next, you need to create and configure the second GitLab project.

1. Head over to your GitLab group gcpvmlinuxvscode, which you created at the beginning of this post.

2. Inside group, gcpvmlinuxvscode, clone the following project:

   git@gitlab.com:tech-marketing/sandbox/gcpvmlinuxvscode/vscvmsetup.git
   

3. Drill into your newly cloned project, vscvmsetup and set up the following CI/CD variables to configure it:

   1. On the left sidebar, select Settings > CI/CD.
   2. Expand Variables.
   3. Set the variable BASE64_GOOGLE_CREDENTIALS to the base64 encoded JSON file you created in project gcpvmlnxvsc. You can copy this value from the variable with the same name in project gcpvmlnxvsc.
   4. Set the variable gcp_project to your GCP project ID.
   5. Set the variable gcp_vmname to your GCP region ID, e.g. us-east1.
   6. Set the variable gcp_zone to your GCP zone ID, e.g. us-east1-d.
   7. Set the variable vm_pwd to the password that you will use to ssh to the VM.
   8. Set the variable gcp_vm_username to the first portion (before the "@" sign) of the email associated to your GCP account, which should be your GitLab email.

Run the project pipeline

After configuring the second GitLab project, manually trigger the provisioning of VS Code and its dependencies to the GCP VM as follows:

1. On the left sidebar, select Build > Pipelines and click on the button Run Pipeline. On the next screen, click on the button Run pipeline.

   The pipeline will:

   • install xauth on the virtual machine. This is needed for effective X11 communication between your local desktop and the VM
   • install git on the VM
   • install Visual Studio Code on the VM.

2. At this point, you can wait until the pipeline successfully completes. If you don't want to wait, you can continue to do the first step of the next section. However, you must ensure the pipeline has successfully completed before you can perform Step 2 of the next section.

Connect to your VM from your local Mac laptop

Now that you have an instance of VS Code running on a Linux VM on GCP, you need to configure your Mac laptop to be able to act as a client to the remote VM. Follow these steps:

1. To connect to the remote VS Code from your Mac, you must first install XQuartz on your Mac. You can execute the following command on your Mac to install it:

brew install xquartz

Or, you can follow the instructions from the following tutorial from the University of North Dakota.

After the pipeline for project vscvmsetup successfully executes to completion (pipeline you manually executed in the previous section), you can connect to the remote VS Code as follows:

2. Launch XQuartz on your Mac (it should be located in your Applications folder). Its launching should open up an xterm on your Mac. If it does not, then you can select Applications > Terminal from the XQuartz top menu.
3. On the xterm, enter the following command:

gcloud compute ssh --zone "[GCP zone]" "[name of your VM]" --project "[GCP project]" --ssh-flag="-Y"

Where:

• [VM name] is the name of the VM you created in project gcpvmlnxvsc. Its value should be the same as the gcp_project variable.
• [GCP zone] is the zone where the VM is running. Its value should be the same as the gcp_vmname variable.
• [GCP project] is the name of your GCP project assigned name. Its value should be the same as the gcp_project variable.

Note: If you have not installed the Google Cloud CLI, please do so by following the Google documentation.

4. If you have not used SSH on your Mac before, you may not have a .ssh in your HOME directory. If this is the case, you will be asked if you would like to continue with the creation of this directory. Answer Y.

5. Next, you will be asked to enter the same password twice to generate a public/private key. Enter the same password you used when defining the variable vm_pwd in the required configuration above.

6. Once the SSH key is done propagating, you will need to enter the password again two times to log in to the VM.

7. You should now be logged in to the VM.

Create a personal access token

The assumption here is that you already have a GitLab project that you would want to open from and work on the remote VS Code. To do this, you will need to clone your GitLab project from the VM. First, you will be using a personal access token (PAT) to clone your project.

1. Head over to your GitLab project (the one that you'd like to open from the remote VS Code).
2. From your GitLab project, create a PAT, name it pat-gcpvm and ensure that it has the following scopes: read_repository, write_repository, read_registry, write_registry, and ai_features
3. Save the generated PAT somewhere safe; you will need it later.

Clone the read_repository

1. On your local Mac, from the xterm where you are logged on to the remote VM, enter the following command:

git clone https://[your GitLab username]:[personal_access_token]@gitlab.com/[GitLab project name].git 

Where:

• [your GitLab username] is your GitLab handle.
• [personal_access_token] is the PAT you created in the previous section.
• [GitLab project name] is the name of the project that contains the GitLab Code Suggestions test cases.

Launch Visual Studio Code

1. From the xterm where you are logged in to the VM, enter the following command:

code

Wait for a few seconds and Visual Studio Code will appear on your Mac screen.

2. From the VS Code menu, select **File > Open Folder..."
3. In the File chooser, select the top-level directory of the GitLab project you cloned in the previous section

That's it! You're ready to start working on your cloned GitLab project using the VS Code that you installed on a remote Linux-based VM.

Troubleshooting

While using the remotely installed VS Code from your local Mac, you may encounter a few issues. In this section, we provide guidance on how to mitigate them.

Keyboard keys not mapped correctly

If, while running VS Code, you are having issues with your keyboard keys not being mapped correctly, e.g. letter e is backspace, letter r is tab, letter s is clear line, etc., do the following:

1. In VS Code, select File > Preferences > Settings.
2. Search for "keyboard". If having issues with the letter e, then search for "board". Click on the "Keyboard" entry under "Application."
3. Ensure that the Keyboard Dispatch is set to "keyCode."
4. Restart VS Code.
5. If you need further help, this is a good resource for keyboard problems.

Error loading webview: Error

If while running VS Code, you get a message saying:

"Error loading webview: Error: Could not register service worker: InvalidStateError: Failed to register a ServiceWorker: The document is in an invalid state."

1. Exit VS Code and then enter this cmd from the xterm window:

killall code

You may need to execute this command two or three times in a row to kill all VS Code processes.

2. Ensure that all VS Code-related processes are gone by entering the following command from the xterm window:

ps -ef | grep code

3. Once all the VS Code-related processes are gone, restart VS Code by entering the following command from the xterm window:

code

Some useful commands to debug SSH

Here are some useful commands to run on the VM that can help you debug SSH issues:

1. To get the status, location and latest event of sshd:

sudo systemctl status ssh

2. To see the log of sshd:

journalctl -b -a -u ssh

3. To restart to SSH daemon:

sudo systemctl restart ssh.service

Or

sudo systemctl restart ssh

4. To start a root shell:

sudo -s

Get started

This article described how to:

• instantiate a Linux-based VM on GCP
• install VS Code and dependencies on the remote VM
• clone an existing GitLab project of yours in the remote VM
• open your remotely cloned project from the remotely installed VS Code

As a result, you can basically use your laptop as a thin client that accesses a remote server, where all the work takes place.

The automation to get all these parts in place was done by GitLab. Sign up for a free 30-day GitLab Ultimate trial to get started today!

In the ever-evolving landscape of software development and operations, the need for streamlined processes, robust infrastructure, and efficient deployment practices has become paramount. DevOps Engineer and Site Reliability Engineer (SRE) roles have emerged as two prominent methodologies to address these challenges. However, bridging the gap between development and operations requires a specialized approach. The latest […]

The article Platform Engineering: What is it? And how it applies to DevOps Engineers and SREs appeared first on Build5Nines.

Episode 0004 - From Portal to Code

Following the conversation on Deployment Stamps, Carl and Brandon dive into the concepts of Infrastructure as Code (IaC) and its applications in cloud computing. The hosts discuss the benefits of using IaC, including version control and transparency in infrastructure deployment, making it easier to understand what has been built and why. They also highlight the importance of governance, such as naming conventions, in IaC, as this can make it easier to find resources later on. Carl and Brandon cover both first and third-party IaC frameworks, as well as pros and cons of each.

Show links:

• IaC Frameworks
  • First-Party (Cloud Provider-Specifid)
    • AWS Cloud Formation
    • Azure ARM/Bicep
    • GCP Deployment Manager
  • Third Party (Cloud Agnostic)
    • Ansible
    • Crossplane.io
    • Pulumi
    • Terraform
      • OpenTofu
• Other Links of Interest
  • Azure Verified Modules
  • Hashicorp Joins IBM
  • How an empty S3 bucket can make your AWS bill explode

Visit us at:

Mike Lyons: The Humility to Listen, A Sprint Planning Turnaround Story

Read the full Show Notes and search through the world’s largest audio library on Scrum directly on the Scrum Master Toolbox Podcast website: http://bit.ly/SMTP_ShowNotes.

When Mike took on one of his early projects, a seemingly small requirement during sprint planning sparked an epiphany about Agile workflows. Mike's story unfolds as he learns that dictating tasks isn't always the best approach, and he realized the importance of listening and empowering those who do the work. What crucial lessons did Mike learn about intellectual humility and creating space for his team to excel? Discover how these insights transformed his approach to sprint planning and why the Toyota Production System might hold secrets to doing your best work.

[IMAGE HERE] Recovering from failure, or difficult moments is a critical skill for Scrum Masters. Not only because of us, but also because the teams, and stakeholders we work with will also face these moments! We need inspiring stories to help them, and ourselves! The Bungsu Story, is an inspiring story by Marcus Hammarberg which shows how a Coach can help organizations recover even from the most disastrous situations! Learn how Marcus helped The Bungsu, a hospital in Indonesia, recover from near-bankruptcy, twice! Using Lean and Agile methods to rebuild an organization and a team! An inspiring story you need to know about! Buy the book on Amazon: The Bungsu Story - How Lean and Kanban Saved a Small Hospital in Indonesia. Twice. and Can Help You Reshape Work in Your Company.

About Mike Lyons

After reading the Agile Manifesto in 2006, Mike focused on making teams and organizations more adaptive and efficient. Despite facing failures and mistakes, these experiences provided him with valuable lessons that enhanced his ability to achieve tangible results with Agile.

You can link with Mike Lyons on LinkedIn.