Axibase Time Series Database

SQL Console

Overview

SQL Console is a web-based interface to submit SQL queries to the database and display the results. The results can be exported into CSV, JSON, and Excel files or reinserted as a derived series in ad-hoc and continuous modes.

SQL Console is located on SQL > Console.

The page has three components:

  1. Query Window
  2. Format Settings
  3. Action Controls

Enter SELECT queries in the Query window and view the results below.

Format Settings

Format Settings apply custom formatting to dates, numbers, and NULL values. Changes apply instantly to the currently displayed records without re-submitting a query.

Date Format / Time Zone

Use the Date Format setting to modify the datetime column without the date_format function in the SELECT expression.

Use the Time Zone drop-down list to display dates in UTC or database time zone.

The table below provides examples of how 2018-05-15 16:30 (UTC) is displayed by SQL Console when the database is configured to Eastern Standard Time (EST):

Date Format Time zone: UTC Time zone: Local
Default 2018-05-15T16:30:00.000Z 2018-05-15T11:30:00.000-05:00
yyyy-MM-ddT HH:mm:ss.SSSZ 2018-05-15T16:30:00.000Z 2018-05-15T11:30:00.000-05:00
yyyy-MM-ddT HH:mm:ssZ 2018-05-15T16:30:00Z 2018-05-15T11:30:00-05:00
yyyy-MM-ddT HH:mm:ss.SSS 2018-05-15 16:30:00.000 2018-05-15 11:30:00.000
yyyy-MM-ddT HH:mm:ss 2018-05-15 16:30:00 2018-05-15 11:30:00
yyyy-MM-dd 2018-05-15 2018-05-15
MMM-dd May-15 May-15
MMM-dd, eee May-15, Tue May-15, Tue
MMM-dd, eeee May-15, Tuesday May-15, Tuesday

TIP

The database time zone can be modified by an administrator.

TIP

To modify the default time zone for a specific query, apply the WITH TIMEZONE clause.

Decimal Precision

The Decimal Precision setting rounds numeric values to the specified number of decimal places. Decimal precision applies to columns of decimal data types: float, double, and decimal.

To disable rounding, revert the setting to -1 which is the default value. When set to a non-default value, the console highlights the setting in light blue.

SELECT MAX(value) AS mx, '123.456' AS num, COUNT(value) AS ct
  FROM mpstat.cpu_busy WHERE datetime > current_day
LIMIT 1
Decimal Precision mx num ct
-1 65.2 123.456 2279
0 65 123.456 2279
1 65.2 123.456 2279
2 65.20 123.456 2279

Seen in the example above, rounding applies only to the mx column which contains decimal values. The num and ct columns are not rounded because they contain string literals and integer values calculated by the COUNT function, respectively.

Theme

Select a color scheme to apply to reserved words and literal values in the query text.

Default Brick Violet

NULL Format

Change the way SQL Console displays literal NULL values.

SELECT NULL
  FROM "mpstat.cpu_busy"
LIMIT 1

This table shows each option applied to a NULL value:

Setting NULL null N/A Dash Empty
Value NULL null N/A -

Action Controls

Execute

Perform the query specified in the Query window and view the results in tabular format below the controls.

Cancel

Interrupt a running query. The database can take several seconds to gracefully stop a query.

Export

Download the results of a query in CSV, JSON (objects), JSON (row), or XLSX format.

Click Export to open the Export Query Results dialog. Modify the query if needed. For example, remove a LIMIT, select a file format, and optionally include metadata.

Store

Store results in the database as a new derived series. Query results are eligible for re-insertion if the SELECT expression contains the required columns.

Execute the query and click Store to open the Store Query Results as Series dialog.

The dialog window provides several tools to configure insertion:

  • Check Last Time: If enabled, the database ignores rows with timestamps earlier than the last insert date for the derived series.

  • Test: Validates the first ten rows returned by the query without storing results and returns an error message if the results cannot be stored.

  • Store: Inserts valid commands into the database.

  • Schedule: Creates a scheduled query with the Store option set to Enabled based on the current query.

Refer to Scheduled Store Documentation for more details.

Query Plan

Open the SQL Query Plan page for the current query.

The plan includes query summary, such as Elapsed Time to perform the query, Returned Records, and the User who performed the query, as well as detailed information such as the number of bytes transferred and records retrieved from storage.

← Bash Client →