Dremio provides the following tools for describing, identifying, and displaying data:
The Wiki allows you to add a description for a Space (and its datasets) or a Source (and its datasets). The Wiki's formatting language is a Github-flavored markdown and supported by a rich text editor.
|Wiki Display||Wiki Markup|
Tags allow you to create and assign tags to all datasets. You can search for items based on a tag and click on a tag to start a search based on that specific tag. The following images shows a dataset in a space with several tags. In this example, "best tag" was selected and is shown in the search field.
The Catalog tab displays Wiki content, Tags, and Fields for all datasets.
To begin a data transformation, via one of the following methods:
- Highlighting a portion or all of a field's value
- Using ropdown menu for the transformation. The dropdown is to the right of the field's name.
The highlighting method is often the most intuitive method.
It provides enough context for Dremio to make some best guesses about how to execute the transformation that you have in mind.
For instance, you could highlight a portion of a field that contains customer names to quickly perform an extract that creates a new field with only last names.
For transformations that are initiated by highlighting part or all of a field value, Dremio uses a heuristic to determine a set of "suggestion cards" that represent its best guesses as to your intended result.
You can click on one of these suggestion cards to inspect a preview of the new dataset and confirm that it matches your expectations. If no suggestion card is a perfect match, you can "flip" the card (by clicking the pencil icon in the upper right corner) to tweak the card's parameters before applying the transformation.
The highlight method is great for beginning an extract. However, in cases where other capabilities are required, the dropdown menu may be more useful.
Field Dropdown Menus
The dropdown menu provides a more complete list of transformations that are applicable to the data type. These include Unnest for complex list type data (See Dealing with Complex Types) and Convert Case for text type data (See Cleaning Text).
Common Transformation Tasks
Fixing Inconsistent Data with a Join
In situations where the entries in a field are inconsistent (for example, different spellings or abbreviations for the same name), the following technique can be used to increase the quality of the dataset:
Identify the field with the problematic data. It may be useful to run this command in the SQL Editor:
SELECT DISTINCT myProblemFieldName FROM myDatasource.myTable
Download the results as CSV using the Download button.
Open the file in a text editor or Excel and create lookup values for the distinct values from your table in a second column. For example, standardizing variations in color names to a single canonical name.
Upload this file to your Home space on Dremio
Open this new dataset and hit the Join button located on the left above the field names
Select Custom Join and then the name of the inconsistent dataset you would like to fix, followed by Next
Drag over the name of the left column from your uploaded dataset, and match it with the name of the field you would like to correct in the inconsistent dataset
Apply the Join then drop the old field, renaming the new one to take its place
Save the corrected dataset
Dealing with Mixed Types
Fields in datasets that contain two or more kinds of data (for instance text and numeric) are said to have a mixed type, and are indicated in Dremio by the mixed type symbol. You should resolve mixed type fields before performing any analyses that rely on them.
To begin cleaning up a mixed type field click the mixed type icon to the left of the field name. This provides a dialog that presents two options for resolving the field:
- Convert to Single Data Type Migrates the field values by attempting to convert them all to a preferred type. If conversion is impossible you can choose to either delete that record, or replace the unconvertable value.
- Split by Data Type Solves the problem of mixed types by creating a new field for each type found within the mixed field.
In this example we resolve a mixed field with both text and numeric types by replacing the records that contain the text 'current' with the integer 2016:
Dealing with Complex Types
You can use Extract to separate a value with a particular path from a map type complex field.
The easiest way to apply this transformation is to expand a map with the
ellipsis button, click the value of interest,
and then select
Extract from the dropdown menu.
Now you can create a field based on the path of the selected value.
[success] Accessing nested fields in SQL
Dremio allows access to nested fields using SQL. This requires aliasing the dataset that contains the nested data, for example:
select t1.property_map.field1 from source.mytable t1
List type complex data is handled with the Unnest transformation, which creates a new record for each element in the list. Each additional record created by Unnest maintains the same values as the original list for all other fields.
You can also use Extract on lists to separate one value or a range of values into a new field. If you extract a range of values the resulting new field will also be a list type.
In this example we use Unnest on a field that contains a list of genres for each title in a database of books:
Before Unnest Transformation
After Unnest Transformation
For text data, excess whitespace and changing capitalization schemes are two common data cleanliness issues. Dremio provides two transformations for dealing with these possible inconsistencies: Trim Whitespace and Convert Case.
In this example, a text field with a mixed capitalization scheme is transformed into consistency:
Handling Invalid, Empty and NULL Values
Fields with invalid values (such as the text 'N/A' in an otherwise numeric column) are marked
with an orange type icon as a 'mixed type' field.
These can be handled by clicking the orange mixed type icon using the dialog that appears to clean up the data.
See Dealing with Mixed Types for more information.
Empty or NULL text values are best eliminated by using Exclude. You can initiate this transformation by:
highlighting a value from the field that contains empty or NULL values, and
selecting Exclude from the dropdown that appears. This renders a list of the values in this field, and the frequency at which they occur.
Check the boxes next to the empty and/or NULL values you which to exclude from the dataset and click Apply.
In the following example, we remove blank values from a field that holds state names:
Working with Dates
In Dremio you can convert a text type field that contains date information into a proper date type field. This allows you to do more sophisticated analyses in external tools such as spotting by trends by month, year, or quarter.
You can begin this conversion by selecting 'Date & Time' from the type menu located to the left of the text field's name. In the subsequent dialog, Dremio provides a dropdown for selecting whether the output should be a time, date, date and time. It also gives a few default options for the format as well as a 'Custom' field for indicating a custom format.
Dremio supports the following formatting elements:
|Format Element||Description||Example Output|
|AD/BC||Era indicator||AD, BC|
|AM/PM||Meridian indicator||AM, PM|
|CC||Century indicator (0-99)||19|
|WW||Week of year (0-52)||4, 43|
|D||Day of week (1-7)||6|
|DY||Abbreviated day name of week||Tue, Fri|
|DAY||Full day name of week||Tuesday, Friday|
|YYYY||Four digits of year||1996|
|YY||Last two digits of year||96|
|DDD||Day of year (1-366)||5, 245|
|MON||Abbreviated month name||Mar, Oct|
|MONTH||Full month name||March, October|
|DD||Day of month (1-31)||24|
|HH/HH12||Hour of day (1-12)||4|
|HH24||Hour of day (0-23)||21|
|TZD||Timezone abbreviation||UTC, PST|
|TZO||Timezone offset||+02:00, -0800|
[info] Usage Information
- Only the following characters are allowed in a format pattern:
- / , . ; :.
- If you need to let a value pass through to the output unmodified, you can surround it with double quotes
- If you convert a date to text, numeric values are zero padded for you.