To analyze data from a data source, files must be formatted into tables in Dremio Sonar. You can then create views from one or more tables. The following figure shows how data is represented in Sonar.
A table contains the data from your source, formatted as rows and columns. A table cannot be modified by Dremio Sonar. A table is represented by the icon .
Maximum Number of Leaf Columns in a Table
The term leaf columns refers to all columns in a table and all leaf-level fields that are used in complex data types in the table. Tables in Dremio are allowed a maximum of 6,400 leaf columns. For example, if a table has 1,000 columns and the data type of one column is a STRUCT that has three fields, the count of leaf columns is 1,003.
Maximum Number of Leaf Columns Scanned Per Table in a Query
The maximum number of leaf columns that a query is allowed to scan per table or per reflection is 800. If a query attempts to scan more than this limit, then you receive this error message:
At most, <limit> columns including leaf level fields of complex type are allowed to be scanned, but the query is scanning <number>. Please include the columns you want to be returned from the query and try again.
If you are trying to use a
SELECT * query to view all columns in a table, you can avoid scanning too many columns by using the
DESCRIBE TABLE command, which returns the name and other details about all columns in the table.
Best Practices for Querying Wide Tables
Dremio recommends these two best practices:
- Create views for querying subsets of the leaf columns in wide tables.
Users who run ad hoc queries directly against wide tables might inadvertently create queries that need to scan more than 800 columns per table. To help users avoid failed queries, create views on subsets of the leaf columns in the wide table and give users access to those views.
- Create reflections to satisfy queries on wide tables.
It is best to restrict the leaf columns included in reflections to those that are most frequently queried. Doing so conserves the resources that are required for storing and refreshing reflections.
A view is a view derived from tables or other views.
Views are not copies of the data so they use very little memory and always reflect the current state of the parent tables or views they are derived from. A view is represented by this icon: .
For a given dataset, by default, you can see the data in a dataset in the Data tab. The other tabs for viewing datasets are shown below:
|1||Details shows the columns in a dataset and lets you add information about a specific dataset in its wiki. You can add searchable labels, which enhances team collaboration because other users can search the labels to trace a specific dataset.|
|2||Lineage shows the data lineage of a dataset. Data lineage refers to the record of how data got into a specific location and the intermediate steps and transformations that took place in its transit.|
|3||Reflections are physically optimized representations of source data.|
Viewing a Table
To view a table:
On the Datasets page, navigate to a data source.
If you have already formatted your table, then select that table. Otherwise, you can format a file or folder as a table.
Click Run or Preview to query the dataset. The Data tab displays the table fields.
Viewing the Metadata of a Dataset
Wherever a dataset is referenced in Dremio Cloud, you can view a metadata card with details about the dataset.
To view the metadata, hover over a dataset to see a metadata card appear with details about the dataset. The components of the metadata card are described below.
|1||Icon represents the dataset format as a table or a view .|
|2||Card title displays the dataset name.|
|3||Reflection icon will appear if the dataset has a reflection.|
|4||Click the SQL Runner button to query the dataset.|
|5||Depends on whether the dataset is a view or a table. For views, there is an Edit button, and there is a Go to Table button for tables.|
|6||The path where the dataset is located.|
|7||Displays any labels that have been applied to the dataset.|
|8||Jobs: The number of jobs run on the dataset in the last 30 days, which links to the Jobs page.|
|9||Descendants: The number of views that are created from the dataset.|
|10||The date and time when the dataset was created.|
|11||The user who owns the dataset.|
|12||The date and time when the dataset was last updated.|
|13||Details Panel: Click this link to open the dataset details panel on the right side of the browser window. In the details panel, you can add or remove labels on the dataset and add or modify wiki content.|
|14||Lineage: Click this link to open the data lineage in a new tab. Data lineage records how data got into the specific location and the intermediate steps and transformations that took place in its transit.|
|15||Columns: The number of columns in the dataset, including the name and data type of each column. Columns with partitions (if any) use a separate partition icon.|
When viewing a metadata card in the SQL Runner’s data panel, the Columns section at the bottom of the card is not displayed because columns can be displayed by expanding the dataset.
Formatting a Table
For formatting a file or folder as a table, see formatting tables and views.
Creating a View
You can create a view from an existing table or view. Create a view from a table by performing the following steps:
To create a view from a table, you can transform the data as required.
Compose the query as required and click Run to validate the query. After running the query, click the arrow next to Save Script As in the top right of the SQL editor, and select Save View as… from the drop-down menu.
Clicking Save View as… prompts you to name the new view and select from a list of spaces where it will be stored. Provide a name and select one space to store it. If the space (path) is not changed, the view gets saved in your home space.
To see the new view, navigate to the space that contains the view on the Datasets page. Click the view to see the data.
Example of Creating a View
This example shows how to create a view from the SF weather 2018-2019.csv table by performing the following steps:
On the Datasets page, trace the SF weather 2018-2019.csv table in the corresponding source folder and click it. To create a view, change the data type of the ELEVATION into number by running the following query.Query to change ELEVATION data type
SELECT TO_NUMBER(ELEVATION, '##.##') AS ELEVATION FROM "SF weather 2018-2019.csv"
Click the arrow next to Save Script As in the top right of the SQL editor, and select Save View as… from the drop-down menu.
Clicking Save View as… prompts you to name the new view and select from a list of spaces where it will be stored. Provide a name and select a space to store it. Let us name the view as SFWeatherElevation. If you do not specify a space (path), the view is saved in your home space.
On the Datasets page, navigate to the space that contains the SFWeatherElevation view. Click the view to see the query and data. You can see the table with a single Elevation column and the following query.Query for SFWeatherElevation
SELECT * FROM SFWeatherElevation
Layering Tables and Views
Dremio recommends that, when you create tables and views, you create them in layers:
- The bottom or first layer consists of your tables.
- In the second layer are views, one for each table, that do lightweight preparation of data for views in the next layers. Here, administrators might create views that do limited casting, type conversion, and field renaming, and redacting sensitive information, among other prepping operations. Administrators can also add security by subsetting both rows and fields that users in other layers are not allowed to access. The data has been lightly scrubbed and restricted to the group of people who have the business knowledge that lets them use these views to build higher-order views that data consumers can use. Then, admins grant access to these views to users who create views in the next layer, without being able to see the raw data in the tables.
- In the third layer, users create views that perform joins and other expensive operations. This layer is where the intensive work on data is performed. These users then create reflections (raw, aggregation, or both) from their views.
- In the fourth layer, users can create lightweight views for dashboards, reports, and visualization tools. They can also create aggregation reflections, as needed.
When hovering over a dataset, you can click on the edit option that appears, which depends on whether the dataset is a view or a table.
For views, there is an Edit button to view or edit the DDL (or original SQL) statement that was used for the dataset. The SQL editor will pre-populate the DDL statement as long as you have privilege access for the dataset.
For tables, there is a Go to Table button to view the metadata and edit the definition of the dataset, because tables cannot be modified. If you want to make edits to a table, you will need to save it as a view.
If you are viewing a table or you don’t have privilege access for the dataset, the default
SELECT * FROM <name of your dataset> statement will be pre-populated instead, as shown in this image:
|1||The Data panel is used to explore your data catalog.|
|2||Collapsing the Data panel hides the panel from view.|
|3||The Data component on the Datasets page is where the SQL editor is located.|
When editing a dataset, a preview of the results will not render by default. The results will only load if you explicitly click Run or Preview.
Starring a Table or View
You can star a table or view that you use frequently, which adds the table or view to your Starred list for easier access.
To star a table or view:
Click on the icon in the side navigation bar to navigate to the SQL Runner.
In the Data panel, find the dataset.
Click on the icon that appears next to the dataset. The dataset will appear on your Starred list.
In addition to starring tables and views, you can star spaces, sources, and other objects in the data catalog. The Starred list can hold up to 25 entities at a time, and each starred item remains on the list even if you open a new browser or clear the cache. To unstar a catalog object, click the Star icon again.
- The starring option is not available for datasets in the Nessie repository.
- Starring is different than pinning items. You can only pin spaces and sources on the Datasets page, and pinned items are not saved if you open a new browser or clear the cache.
Removing a Table
Removing the formatting on a table will revert the table to the folder or file format that it was originally in. For the steps, see Removing Formatting on Data.
Removing a View
Perform the following steps to remove a view:
On the Datasets page, go to the Home Space icon or any space under Spaces, where your view is located.
Under the Action column of a view that you want to remove, click the ellipses (…) icon. From the list of actions, click Remove. Confirm that you want to remove the view.
If you are removing a table or view with children, you get a warning. Removing a table or view with children leaves you with disconnected views that you can no longer query.