LogoLogo
ProductsPricingGlossary Launch Acho
  • 🎉Welcome
  • Introduction
  • What is Acho?
  • How does Acho work?
    • Proof of Concept (PoC)
  • App Builder
    • Get Started
    • Core Concepts
    • Overview
    • App construction
      • App Configuration
      • Pages
      • Interactions
        • Add an interaction
        • Add conditions to interactions
        • Event payload
        • Ordering interactions with action flow
        • Transformer
        • Actions
          • Navigation
          • Element
          • Data Source
          • App
          • Page
          • API Service
          • Database
          • Media Service
      • Table
      • Metric
      • Query
      • Data Store
      • Elements
        • Table & Chart
          • Searchable Table
          • Table
          • Chart
          • Score Card
        • Form Elements
          • Form
          • Search Bar
          • Checkbox
          • Date Picker
          • Custom Form
          • Input
          • Multiselect
          • Radio Button
          • Radio List
          • Rich Text Editor
          • Select
          • Switch
          • Textarea
          • Upload
        • Web Elements
          • Badge
          • Button
          • Clickable
          • Collapse Menu
          • Divider
          • Icon
          • Image
          • Link
          • Message
          • Modal
          • Notification
          • Pagination
          • Popover
          • Rate
          • Rich Text
          • Tabs
          • Text
        • Layout Elements
          • Container
          • List
        • Advanced elements
          • Code Block
          • Condition
          • Print
        • CSS Styles
          • General techniques
          • Layout
          • Spacing
          • Size
          • Position
          • Typography
          • Background
          • Border
          • Effect
        • Form Check
        • Tooltip
      • Accessors
      • Plugin Store
    • Popular Use Cases
      • Build a chart from Table Nodes
      • Create a table
      • Create a list
      • Create a filter
      • Create a search bar
      • Use Custom Form Container to collect user inputs
      • Drill down on a table
      • Download file from query node
      • Set loading animations
      • Modify a database
      • Navigate to a different page
      • Set up dynamic routing
      • Update app data using accessors
      • Create a Chart with Code Block and D3 Library
      • Rich text and rich text editor
      • Send an Email via Mailjet Plugin
    • Preview
    • Publish
      • Embed published app on website
    • Version Control
    • App User Management
      • Onboarding Page
      • Sign in page
      • Guest Isolation and Private Session
    • Theme
    • FAQ
  • Acho Studio
    • Quick Start
    • Resources
      • Add a resource
      • Supported data sources
        • Airtable
        • Amazon Ads
        • Amplitude
        • Amazon Seller Partner
        • Apify Dataset
        • Appstore
        • Asana
        • API Connector
        • API Template
        • AWS CloudTrail
        • Azure Table Storage
        • Batch Files
        • Bamboo HR
        • BigCommerce
        • BigQuery
        • Bing Ads
        • Braintree
        • Cart.com
        • Chargebee
        • Chargify
        • Chartmogul
        • Clickhouse
        • Close.com
        • CockroachDB
        • Confluence
        • Customer.io
        • Delighted
        • Dixa
        • Drift
        • Facebook Marketing
        • Freshdesk
        • Freshsales
        • Freshservice
        • GitHub
        • GitLab
        • Google Ads
        • Google Analytics
        • Google Sheets
        • Google Search Console
        • Greenhouse
        • Harness
        • Harvest
        • HubSpot
        • IBM Db2
        • Instagram
        • Intercom
        • Iterable
        • Jenkins
        • Jira
        • Kafka
        • Klaviyo
        • Kustomer
        • Lemlist
        • Lever Hiring
        • LinkedIn Ads
        • Linnworks
        • Mailchimp
        • Marketo
        • Microsoft Teams
        • Mixpanel
        • Monday
        • MongoDB
        • MySQL
        • My Hours
        • NetSuite
        • Notion
        • Okta
        • OneDrive
        • OneSignal
        • Oracle DB
        • Outreach
        • PagerDuty
        • Paypal Transaction
        • Paystack
        • Pipedrive
        • PersistIq
        • Pinterest
        • Plaid
        • PostgreSQL
        • PostHog
        • PrestaShop
        • Qualaroo
        • QuickBooks
        • Recharge
        • Recurly
        • Remote File
        • S3
        • Salesforce
        • Salesloft
        • SearchMetrics
        • Sendgrid
        • Sentry
        • Short.io
        • Shopify
        • Single File
        • Slack
        • Smartsheets
        • Snapchat Marketing
        • Snowflake
        • SQL Server
        • Square
        • Stripe
        • SurveyMonkey
        • Tempo
        • TikTok Marketing
        • Trello
        • Twilio
        • Typeform
        • VictorOps
        • WooCommerce
        • YouTube Analytics
        • Zendesk Chat
        • Zendesk Sunshine
        • Zendesk Talk
        • Zendesk Support
        • Zenloop
        • Zoho CRM
        • Zoom
        • Zuora
    • Data Prep (Projects)
      • Create a Data Prep Project
        • Create a table tab
        • Copy a table tab
        • Hide a table tab
      • Apply an action
        • View
          • Hide Columns
          • Filter
          • Sort
          • Cut
          • Rename
          • Move Column
        • Combine Tables
          • Join
          • Union
        • Transformation
          • Pivot Table
          • Unpivot
        • Data Cleaning
          • Cleanse
          • Replace
          • Change Type
          • Deduplicate
          • Split
          • Flatten
        • Tools
          • Formula
          • SQL Editor
            • SQL Editor Queries
            • All functions in Formula/SQL
              • Mathematical Functions
              • String Functions
              • Date Functions
              • Datetime Functions
              • Time Functions
              • Timestamp Functions
              • Conditional Functions
              • Type Conversion
              • Other Functions
        • Reserved Keywords
      • Pipeline
      • Edit your pipelines
      • Generate column summary for inspection
      • Use URL to access and share your project
      • Write a description for your project
      • Delete a resources/project/tab
    • Data Flow
      • Export data
        • Download CSV files
        • Excel Connector
        • Google Sheets Connector
        • Embed a table on your website
          • Data Usage Agreement
        • Apache Superset
        • Chartio
        • Looker
        • Metabase
        • Power BI
        • Qlik
        • Tableau
        • MongoDB
        • MySQL
        • MSSQL
        • Snowflake
        • PostgreSQL
        • BigQuery
    • Set up Data Sync
    • FAQ
  • Organization
    • Get started
    • Create an organization
    • Invite people to your organization
    • Share your resources
    • Share your projects
    • Share your apps
    • Change payment method on file
  • FAQ
    • I can't find data after uploading a big CSV file
Powered by GitBook
On this page
  • Set up a query
  • Edit Queries
  • How do queries work?
  • Queries Syntax
  • Set SQL Parameters
  • Macros
  • Accessor
  • Supported Events

Was this helpful?

  1. App Builder
  2. App construction

Query

PreviousMetricNextData Store

Last updated 1 year ago

Was this helpful?

Queries allow you write SQL queries to retrieve data from your data sources or modify your databases in queries.

Queries must be created from Tables and are only available in the current app. However, you can have multiple queries for the same tables.

All elements across pages can interact with Queries through interactions, including trigger Run Query or Set SQL Parameters.

Set up a query

Queries must be created from Tables and are only available in the current app. There are two methods to instantiate a query:

  • From Tables

    1. Navigate to the desired table.

  • Adding from Query Tab

    1. Go to the Query tab.

Edit Queries

After creating the queries, double click on it to edit.

  1. Queries Name: In app name of your query

  2. Database Name: Represents the database you are currently querying against.

  3. 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 query, 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.

  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. Macro is supported to help you do complex operation Macros

  5. Generate by AI: Click on this button to access the AI panel, which leverages generative AI to assist you in writing SQL queries. You can streamline the query creation process using natural language prompts.

  6. 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.

  7. Save: Run the query and save all the changes (including the query, query results, and parameters).

  8. Table Preview: When you run a query, the results will be presented in the Table Preview. Use the pagination below to switch page.

How do queries work?

Each queries functions like a script, storing an SQL query. When triggered by other elements, the query node fetch information including database address from table node, then run the query, either retrieving data or modifying the database. If the query returns data, the data is stored in Acho's in-memory database, serving as a cache layer for your app. However if your statement is DML (UPDATE, INSERT, DELETE) or DDL(CREATE, DROP), no data will be stored in the query.

Queries Syntax

The syntax of a queries is determined by the data source they connected

  1. At present, it's recommended to use only SELECT queries in such queries. Any data modifications made using DML (UPDATE, INSERT, DELETE) or DDL (CREATE, DROP) may be overwritten when the data source is synced, resulting in the loss of those changes.

  2. Direct Connector: Query against your database directly. It allows you to query all data stored in your database and supports DQL (SELECT), DML (UPDATE, INSERT, DELETE) or DDL(CREATE, DROP). Different databases have their own SQL dialects and syntax. Currently, we support the following databases for direct access: - PostgreSQL - MySQL - Snowflake - MongoDB

Set SQL 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 Set SQL Parameters. See detailed example in Create a filter.

1. Add new parameter

Click the plus icon at the Parameter Panel to add new parameter. You need to specify name and datatype and Default value(optional but recommended).

2. Insert parameters in SQL query

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}}

3. 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")
{% set comma = joiner() %}
SELECT *
FROM customers
WHERE category IN ( {% for category in categories} {{comma}} '{{category}}' {% endfor %})

{
categories: [
    "first_name",
    "last_name",
    "type",
    "last_login"
    ]
}
SELECT {{ columns|join(',') }}
FROM customers

Macros

Macro is a templating language that enhances queries to make them dynamic. It supports various functionalities, including for loops and conditional statements (if and else).

Accessor

To access a query elsewhere in the app, use ${#query_name}. See details in Accessors

You may need to rename your query to valid variable name to use it in accessor

Supported Events

Data Update: Triggered when the query runs.

Data Update Error: Triggered when errors occur as the query runs.

Click on the More option

Select Create Query .

Click on Add Query.

Choose the table from which you wish to create the query.

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 to learn more about how to set up parameters.

Acho resource and Data Prep Projects: Acho resource and Data Prep Projects are replicated and stored in an Acho-hosted data warehouse. For query nodes connected to resources and the Data Prep Project, use BigQuery syntax. See which and you can use.

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 "" to do some transformation to the array parameter. 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.

You can also use an array to select multiple columns. Suppose you have a parameter called columns, which you can dynamically set by . By default, columns is an array that contains four column names.

See available macros template at

here
statements
functions
https://mozilla.github.io/nunjucks/templating.html
macro
Macro
Set SQL parameters
Editing query
A text accessing the query 'car_datebase'