Amorphic Playground
This powerful tool allows you to harness the full potential of your data using familiar SQL queries. Whether your datasets are stored in Amazon S3 or Amazon Redshift, our Playground provides a seamless interface to analyze, explore, and extract insights from your data.
With the Amorphic Playground, you can:
- Run standard SQL queries against your datasets
- Generate sample queries to help you get started
- Track your query history for future reference
- Download query results for external analysis
- Seamlessly work with different storage backends (S3/Redshift)
This guide will walk you through everything you need to know to make the most of the Playground's capabilities.
Getting Started
The domonstration below illusatrates the use of different kind of operations available in QueryEngine.
Query Editor Interface
The Query Editor is your workspace for crafting and executing SQL queries. Simply type your SQL query in the editor and execute it against your selected datasets.
Quick Tips:
- Clear the editor using the button in the bottom right corner
- Use the "Generate Sample Query" feature if you're not sure where to start
- Select the appropriate query target (Athena or Redshift) based on your data location
Generating Sample Queries
Not sure how to structure your query? Let us help you get started:
- Select your Domain and Resource from the dropdown menus
- If querying a Redshift cluster, select the appropriate Tenant
- Click "Generate Sample Query"
- A sample query referencing your dataset will appear in the Query Editor
- Modify the sample query as needed for your specific analysis
Working with Queries
Supported Query Types
The Amorphic Playground supports different SQL commands based on the dataset type:
| S3Athena and Redshift Datasets | Iceberg Datasets | Hudi Datasets |
|---|---|---|
| SELECT | SELECT, INSERT INTO, UPDATE, DELETE FROM | SELECT, INSERT INTO, UPDATE, DELETE FROM |
| MSCK REPAIR TABLE | DESCRIBE, SHOW TBLPROPERTIES | DESCRIBE, SHOW TBLPROPERTIES |
| CREATE TABLE | SHOW PARTITIONS | SHOW COLUMNS, ALTER TABLE |
| CREATE EXTERNAL TABLE | OPTIMIZE, MERGE INTO | VACUUM |
| SHOW PARTITIONS | SHOW COLUMNS |
Query Target Location
Choose where to execute your query based on your data location:
Athena (default): For datasets stored in Amazon S3 Athena.
LakeFormation: For datasets stored in Amazon LakeFormation.
Redshift: For datasets stored in Amazon Redshift.
Workgroup Selection
When using Athena, you can select specific workgroups for query execution:
Default: This is the "primary" workgroup
Iceberg datasets: Require the "AmazonAthenaEngineV3" workgroup
Other datasets: Can use the default "primary" workgroup.
For Merging the queries user needs a V3 workgroup.
Using Assume Role Feature
Enable the "Assume Role" option when you need to:
- Query datasets with your specific IAM permissions
- Access Lake Formation views with Athena
This role cannot be used for system datasets or if you have more than 220 datasets assigned to you.
Query Syntax and Best Practices
Proper Dataset Referencing
| Target Location | Reference Schema Format |
|---|---|
| Redshift resources | tenant_name.schema_name.resource_name |
| S3 Athena resources | schema_name.resource_name |
| Column access | tenant_name.schema_name.object_name.column_name |
When using reserved keywords in your queries, enclose them in double quotes. For instance,
'GROUP' is a reserved keyword, querying dataset where one of the column is 'GROUP'
-- Incorrect usage with reserved keyword
SELECT GROUP FROM domain.dataset
-- Correct usage with reserved keyword
SELECT "GROUP" FROM domain.dataset
For official documentation on reserved keywords, refer to:
Managing Query Results
Viewing Results
After successfully running a query, you can:
- View up to 50 records directly in the Amorphic platform
- Download the complete result set for external analysis
Query History
Access your past query executions in the History tab located next to the query editor. This helps you:
- Track your analysis progress
- Reference successful queries
- Troubleshoot failed queries
Redshift query results are stored temporarily and expire 24 hours after execution.
API Integration
Execute queries programmatically by making a POST call to /queries with these parameters:
- QueryString: Your SQL query string (required)
- Encoding: Specify how the query is encoded (optional)
- none: Default for most queries
- base64: Required for queries with special characters (e.g., '%%')
Most queries can be run using Encoding set to none . However, you may encounter a forbidden issue due to WAF rules if QueryString contains strings like '%%'. To avoid this issue, we suggest using Encoding set to base64 and providing a base64 encoded QueryString.
Troubleshooting
Common Errors
If you encounter issues with your queries, check these common causes:
- Data-schema mismatches
- Invalid SQL commands
For detailed error information, consult AWS Documentation mentioned below:
Limitations and Considerations
- Maximum of 5,000 rows returned per query
- Redshift datasets cannot be queried alongside non-Redshift datasets
- Cross-tenant querying supported only for Redshift nodes of type "ra3"
When working with unsupported data types, refer to AWS Athena supported data types
Stop Query Option
Users have the option to stop a query whenever necessary during execution by clicking the "Stop Query" button.
Playground dropdowns will display only the user-accessible domains/datasets.