View Builder allows you to create new tables (views) based on the result-set of an SQL statement.
The view contains rows and columns from one or more tables within a database that already exists within your organisations Manage Data.
You can create, alter or drop (delete) views from within a database.
View builder could also be used to validate the table within a database contains the data that is expected eg. you could use View Builder to view a subset of rows from a table or to count the number of rows that meet a certain criteria.
In this article, you will learn:
You will have:
- Uploaded data onto the Data Republic platform
- Created a database and tables
- Loaded data into the tables
How to use View Builder
1. From the menu, select View Builder.
Your databases will be listed.
2. Click the arrow next to the database to show the tables that you can create a view for.
3. Click a table to work with.
4. Write the SQL for your view in the black text window.
5. Click the arrow to run the query.
6. Save your view by clicking the 'Save' icon found below.
Note: Once you have created a view of a table, you will not see the view listed under the Databases tab in Manage data. However, when you proceed to create a data package, you will be able to select from a list of all tables and views you have created.
Make sure you prefix your database name with your Organization ID
- Refer to screenshot below. All organizations will have a unique id.
- If database names are not prefixed with your Organization ID and run a query, the ‘Query result’ tab will display an error.
- For example, if your Organization ID is o_ABCDEFGHIJKL a query could be:
“SELECT * FROM o_ABCDEFGHIJKL_<database name>.<table name>”
To cancel a query
- Open a new ‘View Builder’ tab and go to “Queries History”
- Click the three dots next to your specific query and select ‘Remove’.
To query a database with incorrect data types
- Use the CAST() function, this enables you to convert your data from one data type to another.
- This is useful when there is an incorrect classification of data types, which may prevent you from performing certain operations such as aggregation or filtering operations.
- For example, you may want to query the SUM of a column yet the data is classified as 'strings'. You would need to cast the column as an INT (integer) or FLOAT before performing the operation.
- An example of how to use the CAST() function:
SELECT SUM(CAST( <field> AS <data type> ))
Column Data Type Descriptions
Common Error Messages
If you are unable to resolve an error, please contact DR Support at email@example.com or via support-chat on the bottom right.