Use datasets and variables

Usage notes

Before you proceed, understand the following concepts.

• original dataset: The raw data used for query analysis, including databases and file datasets.

  • Database: Data from a database or data warehouse, such as AnalyticDB for MySQL, Hologres, or MaxCompute. Before you can use a database, you must create a corresponding data source in DataV-Note.

  • File dataset: Data from local files, such as Excel files. Before you can use a file dataset, you must import the local file into DataV-Note. For more information, see Add file data.

• query result set: Data generated by filtering or processing an original dataset based on specified conditions. You can use this data for further analysis in SQL or Python analysis cells or as input for visualizations, such as charts.

• variable: An output from an analysis cell, such as an SQL query result, a result from a filter, or a parameter from a control. Variables can be referenced in SQL or Python to build dynamic queries.

Prerequisites

• You have created a project. For more information, see Create a project.

• You have created a data source on demand or imported a local file. For more information, see Data Source Management.

Open the project editor

1. Log on to the DataV-Note console.

2. On the All Projects > All tab, hover over the target project and click Edit to open the project editor.

   On this page, you can use original datasets or query result sets for query analysis, or use variables to build dynamic queries.

Use an original dataset

Follow these general steps to use an original dataset for query analysis and visualization:

1. Create an analysis cell.

   Datasets are used within analysis cells. You can create analysis cells such as SQL, Python, or intelligent analysis.

2. Select a dataset for query analysis.

   You can select a database or a file dataset as your data source and use queries to perform analysis and create visualizations.

   Note

   For more information about how to use databases and file datasets, see the following sections.

   The following example demonstrates how to use SQL to perform query analysis on a dataset.

   Use a database

   The Datasets > Databases section lists all data sources that have passed a connectivity check. You can use this data for query analysis and visualization.

   Note

   • To view a complete list of all data sources, including those with successful, failed, or untested connections, go to the Data Source Management page.

   • If no suitable data source is available, click the icon and follow the on-screen instructions to create one.

   Example: Use an SQL analysis cell to query data from the nation table in a Hologres data source.

   

   Use a file dataset

   The Datasets > File Datasets directory lists all imported local files. You can use this data for query analysis and visualization.

   Note

   • If no files are available, click the icon to upload a file. For more information, see Add file data.

   • If you upload an Excel file that contains multiple sheets, you must specify which sheet to query in your SQL code.

   Example: Use an SQL analysis cell to query data from an Excel file and display the results in a chart.

   

Use a query result set

A query result set typically contains the final data for your analysis report. You can use this data for further analysis in SQL or Python analysis cells, or as input for a visualization, such as a chart, table, map, or crosstab.

1. Generate a query result set.

   You must first perform query analysis on a database or a file dataset. The result of the analysis is a query result set.

   Note

   The generated query result set appears in the Datasets > Query Result Sets directory on the left.

   The following example shows how to perform query analysis on a file dataset to generate a query result set.

   You can change the type of the query result set (labeled 3 in the image). The following types are supported:

   • view: Queries on this view retrieve data directly from the source table in the original file dataset or database.

   • cached view: This view caches the relevant data locally in DataV-Note to accelerate queries and reduce the load on the source table.

   • DataFrame: Allows you to manipulate data using Pandas in a Python analysis cell.

2. Analyze the query result set.

   • Secondary data analysis: You can create SQL and Python analysis cells on demand and execute the corresponding analysis code.

     Example: Use an SQL analysis cell to aggregate data from the result_1 and result_2 result sets and display the results in a chart.

• Visualization analysis: You can create a visualization analysis cell and select a specific query result set for visualization. The following example uses a stacked bar chart to display data from the retail query result set.

  

Manage datasets

You can perform the following management operations in the dataset list. The available operations vary depending on the dataset type.

• Copy a table or file name, create an SQL query based on a selected dataset, edit file data, or delete a dataset.

  Important

  • Only file datasets can be deleted. This operation cannot be undone. Proceed with caution.

  • You can edit only .XLSX and .CSV files. For more information, see Edit file data.

  

• View table details.

  

  • Schema: Displays information about the table, such as field names and data types. The specific information shown varies by table.

  • DDL: The Data Definition Language (DDL) statement for the table, provided for reference only. This information is available only for database tables.

  • Sampled Data: Displays a sample of the table's data. To view the full dataset, use an SQL analysis cell.

Use variables

In the left-side navigation pane of the project editing page, click the icon to open the Variables tab. This tab lists all outputs from the analysis cells in the current project, which serve as variables. These outputs can include SQL query results, results from a filter, or parameters from a control. You can reference these variables in SQL analysis, Python analysis, and visualization analysis to build dynamic queries.