With advance dataset authoring, now users have the ability to create datasets using SQL. With this feature, users have more flexibility to create the desired dataset without any restriction.
In the create dataset wizard, you will now see two tabs. Basic Mode and SQL Mode.
Basic mode will have all the visual configurations which you can use to create a dataset. If you click on SQL mode tab, you’ll see a SQL editor. Here you can write SQL queries and create a dataset. SQL to BASIC mode conversion is not supported, if user switches from SQL to BASIC they either see existing BASIC config or nothing if there was nothing saved for BASIC config. We show popup message when user switches between modes.
The SQL editor has autocomplete. You can press Ctrl + Space to trigger the autocomplete suggestion and select an option.
The autocomplete suggestions changes based on the context. Refer the below example on how you can construct a query. Apart from using editor you can copy paste queries to execute in SQL mode. Projections and Filters used in SQL should be Data Store name for respective fields of an entity. Entity name should be similar to Data Store name of an entity.
Create and edit variable.
Conversion from Basic to SQL mode
Note: Similar to Basic mode for filters you can relative time like “last/next/before/after N days/years/months”, “today, this week/month/year”
Note: Data manipulation queries like Insert, update, delete, drop, truncate are not supported. SQL queries only support read queries.