The Advanced Search feature allows you to write and run SQL queries to explore data efficiently. It is designed for read-only data analysis, with tools that help you build, manage, and reuse queries while understanding the underlying database structure.
Navigate to the Search page and select Advanced Search.
On the “Advanced Search” page, you can create worksheets, explore databases, write SQL queries, run them, and download the results. Queries must be written in your datalake’s SQL dialect to execute properly.
Database Explorer
The Database Explorer helps you understand the data structure in your datalake before writing queries.
Navigate through Schemas, Tables, and Columns. For each column, you can view the Data type (for example, number, text, array, variant)and whether the column is a primary key.
From the three‑dot (⋮) menu next to a table, you can:
View Sample Data: Displays a sample of records from the selected table to help you understand the data.
Copy Table Name: Copies the fully qualified table name to your clipboard for use in SQL queries.
Generate SELECT Statement: Automatically generate a basic
SELECTquery for the table.
Worksheets
Worksheets help you organize and manage multiple queries within Advanced Search.
Create and Manage Worksheets
Add a new worksheet: Select + to create a new worksheet.
Rename or delete a worksheet:
From the worksheet tab, select the three‑dot (⋮) menu, then choose Rename or Delete Worksheet.
From within an open worksheet, select the three‑dot (⋮) menu to rename or delete it.
Closing a worksheet only removes it from the current view and tabs. The worksheet still exists and can be re‑opened at any time from the sidebar.
Warning:
SQL Query Limitations: Advanced Search supports read-only SQL operations only. Data modification operations such as
INSERT,UPDATE,DELETE, andDROPare not permitted.When the same table name exists in multiple schemas, queries default to the cdp schema unless a different schema is specified (for example,
ocsf.device).
Type‑Ahead Assistance
As you type a query, Advanced Search provides real‑time suggestions, including SQL functions, table names, column names, and keywords. This helps reduce errors and speeds up query creation.
Running Queries and Viewing Results
Enter your query and then select Run to execute it.
Scroll down to view the results displayed in a tabular format. If the result set is large, use the pagination controls to navigate through multiple pages. You can Download the query results in CSV or JSON format.
Code Versions (Query History)
The Code Versions feature lets you reuse previous queries.
Copy to Clipboard: Copy the loaded query directly to the clipboard for reuse or modification.
Load Query: Load a previously executed query from history.
This is useful for auditing past queries or building new ones based on earlier work.
More Actions
Use More Actions to quickly work with your SQL queries:
Format Query: Automatically formats SQL for better readability.
Copy Query: Copies the current query to the clipboard.
Load from File: Upload a saved SQL file into the editor.
Save to File: Download the current query as a file.
SQL Syntax Reference: Opens the SQL syntax reference for guidance on supported syntax and functions for your configured datalake.
