/api/2.0/preview/sql/queries API only returning certain queries

[dbengineer516]

Hello,

When using /api/2.0/preview/sql/queries to list out all available queries, I noticed that certain queries were being shown while others were not. I did a small test on my home workspace, and it was able to recognize certain queries when I defined a function to take in a parent folder. The goal was to be able to see all queries within a specified "parent," but the API call only returned back under half of the query files I expected in the parent/subfolders under the parent. Is there a reason why some query files are shown while others are not? Please advise.

Thank you!

[brockb]

Hi,

How many queries were returned in the API call in question? The List Queries documentation describes this endpoint as supporting pagination with a default page size of 25, is that how many you saw returned?

Query parameters
page_size integer <= 1000
Default 25
Number of queries to return per page.

Thanks.

[dbengineer516]

Hi @brockb,

The count of queries I got returned from this call was 732. What I tried to accomplish was being able to view all queries under a certain parent folder. See code below:

import requests

import json

def get_queries_in_parent(parent):

    # Define the necessary parameters

    sql_api_url = '<workspace_url>/api/2.0/preview/sql/queries' #workspace_url replaced with my workspace url

    api_token = '<token>' #token replaced with my actual access token

    # Set up the headers with the API token

    headers = {

        'Authorization': f'Bearer {api_token}',

        'Content-Type': 'application/json'

    }

    # Make the GET request to the SQL API

    response = requests.get(sql_api_url, headers=headers)

    # Extract and print the list of queries

    if response.status_code == 200:

        response_json = response.json()

        print(response_json)

        queries = response_json.get('results', [])

        for query in queries:

            query_id = query.get('id')

            query_name = query.get('name')

            query_text = query.get('query')

            query_parent = query.get('options', {}).get('parent')

            if query_parent == parent:

                 print(f"Query ID: {query_id}, Name: {query_name}, Query Text: {query_text}, Parent: {query_parent}")

    else:

        print(f'Request failed with status code: {response.status_code}')

        print(response.json())

# Specify the parent workspace folder

parent_folder = 'folders/123456789123' # example parent folder

# Call the function to get queries in the specified parent workspace folder

get_queries_in_parent(parent_folder)

I also searched by id and name to see if certain queries even existed in my list, and a good handful of queries were not in the original list.

Thank you!

[dbengineer516]

Hi @brockb,

You were correct. I was only grabbing the first page of results, so I updated my code to traverse each page to grab all queries and add each page's results into a list.

Thank you!