Data Nodes
Data nodes are one of the essential components in the App Builder. Data nodes are the primary data source of your app. They allow your app to talk to your existing data sources or databases. You can write queries to retrieve data from your data sources or modify your databases in data nodes. Data nodes can be dragged from the Data tab in the Create panel and placed outside pages. All elements across pages can interact with them.
Each data node is like a script. All data nodes store a single query (SQL or other query languages) as a script. When they are triggered by other elements, they run the query to retrieve data or modify the database. If the data node returns data, the data is stored in Acho's in-memory database, which acts as a cache layer for your app. If your statement is DML (UPDATE, INSERT, DELETE) or DDL(CREATE, DROP), there will be no data stored in the data node.
There are 3 types of data nodes: Acho resource, DB Direct, and Transformation Node.
- 1.Acho resource: Query against data stored on Acho. Generally, when you connect to your data sources (except for direct access), data will be replicated and stored in an Acho-hosted data warehouse. Acho resources support querying against various data sources with the same SQL dialect and process vast amounts of data faster. Currently, it only supports DQL (SELECT). See which statements and functions you can use.
- 2.Database direct: Query against your database directly. It allows you to query all data stored in your database and supports DQL, DML, and DDL. Different databases have their own SQL dialects. Currently, we support the following databases for direct access: - PostgreSQL - MySQL - Snowflake - MongoDB
- 3.Transformation node: Query against data stored in the previous two types of data nodes. It allows you to combine data sources from multiple different databases or further transform data for different uses. Transformation nodes require users to use AlaSQL. See which statements and functions you can use.
A main difference between a database node and a transformation node is what data they query against.
Database nodes allow you to query directly against your database without copying your tables into Acho. Only when the query returns a result, such as one with a
SELECT statement, will the extracted data be stored in our in-memory database. You can also write statements to create, modify, and delete tables in your database directly from the database nodes. We support direct connections to PostgreSQL, Snowflake, MySQL, and MongoDB.
On the other hand, transformation nodes can only query against what's already stored in the in-memory database. For example, if your database node returns a result, a transformation node will be able to query against that result. However, it won't be able to access the database itself.
- 1.Database Name: Represents the database you are currently querying against.
- 2.Data Directory: Displays all the available tables across different databases within the same database type, including Acho-hosted and self-hosted databases. For example, in the PostgreSQL data node, you can find all the available PostgreSQL databases, but it doesn't include other types of databases (such as MySQL). Also, you cannot query against tables across different PostgreSQL databases simultaneously.
- 3.Table Preview: When you run a query, the results will be presented in the Table Preview. The preview only displays the first 100 rows.
- 4.Query Pane: The pane allows you to write queries. It supports writing a single query at a time. Currently, it doesn't support writing functions or procedures.
- 5.Parameter Pane: This pane allows you to create parameters and their initial values. These parameters are dynamic and can be used in the Query Pane. See here to learn more about how to set up parameters.
- 6.Cancel: The cancel button is to close the query editor without saving anything.
- 7.Run: Run the query in the Query Pane and display the query results in the above Table Preview. Note that it's only to run the query but won't save it and its results.
- 8.Save: Run the query and save all the changes (including the query, query results, and parameters).
Parameters are used for creating dynamic queries. They are like variables that can store any value and be inserted into queries. Their values can be changed via event actions.
1. Activate the parameter pane
By default, the parameter pane is invisible. To open the parameter pane, you have to create variables by typing
@parameter_name in the Acho Resource nodes or {{parameter_name}} in other database nodes.2. Specify the default value of the parameter
All the parameters are stored in a JSON format. JSON supports 4 data types: string, number, boolean, and array. See the example below.
{
"name": "James",
"age": 31,
"is_repeat_customer": true,
"category": ["New", "Repeat", "Royal"]
}
JSON consists of one or more key-value pairs. The keys, such as name, and age, are the parameter names. Their values are the value passed into the query.
3. Insert parameters
All parameters can be inserted anywhere in the query. The parameter replaces
{{parameter_name}} with the value specified in the parameter.Here are some examples:
Pass a string: (need quotes to represent it as string)
SELECT *
FROM customers
WHERE customer_id = '{{customer_id}}'
Pass an integer or a number: (doesn't require quotes)
SELECT *
FROM customers
WHERE age = {{customer_id}}
Pass a column name: (doesn't require quotes)
SELECT {{category}}, COUNT(DISTINCT customer_id) AS n_customers
FROM customers
GROUP BY {{category}}
4. Deal with arrays or objects
If your parameter is an array, you have to use "macro" to turn the array or object into a format that the database can ingest.
Use an array to specify values in the IN statement
Suppose you have a parameter called
categories. It's an array and contains three values.{
categories: ["New", "Repeat", "Royal"]
}
Now, we want to create a query to filter the
customer table and get all the customers in these three categories via the IN statement as shown below.SELECT *
FROM customers
WHERE category IN ("New", "Repeat", "Royal")
However, the array cannot be passed to the query directly since the query doesn't need the square brackets. In this situation, we need to use "macro" to do some transformation to the array parameter. Macro is a templating language that can help you generate queries. It supports various functionalities, such as for loop, or conditional statement (if and else) In this example, we want to use the for loop to pass all the values in the array one by one and use
joiner() the function in macro to concatenate all the values with a comma.{% set comma = joiner() %}
SELECT *
FROM customers
WHERE category IN ( {% for category in categories} {{comma}} '{{category}}' {% endfor %})
You can also use an array to select multiple columns.
{% set comma = joiner() %}
SELECT {% for column in columns} {{comma}} {{columns}} {% endfor %}
FROM customers
Data Update: Triggered when the data node runs.
Last modified 1mo ago