SELECT

Syntax

LIMIT {[offset,] row_count | row_count OFFSET offset}

Remarks

• The LIMIT clause constrains the number of rows returned by the SELECT statement.

• Both the arguments must be non-negative integer constants.

• The row_count specifies the number of rows to return from the beginning of the result set, and the offset specifies the offset of the first row to return.

• The offset of the first row in a table is 0 (not 1).

Examples

SELECT * FROM hrRec;

+-----------+-----------+----------+--------+
| FirstName | LastName  | City     | Tenure |
+-----------+-----------+----------+--------+
| Adam      | Gale      | Brooklyn |     40 |
| Samantha  | Beck      | New York |     44 |
| Clara     | Wakefield | DC       |     24 |
| Todd      | Bridges   | DC       |     30 |
| Ron       | Fletcher  | New York |     23 |
+-----------+-----------+----------+--------+

SELECT * FROM hrRec LIMIT 2;

+-----------+----------+----------+--------+
| FirstName | LastName | City     | Tenure |
+-----------+----------+----------+--------+
| Adam      | Gale     | Brooklyn |     40 |
| Samantha  | Beck     | New York |     44 |
+-----------+----------+----------+--------+

SELECT * FROM hrRec LIMIT 1,2;

+-----------+-----------+----------+--------+
| FirstName | LastName  | City     | Tenure |
+-----------+-----------+----------+--------+
| Samantha  | Beck      | New York |     44 |
| Clara     | Wakefield | DC       |     24 |
+-----------+-----------+----------+--------+

SELECT * FROM hrRec LIMIT 2 OFFSET 1;

+-----------+-----------+----------+--------+
| FirstName | LastName  | City     | Tenure |
+-----------+-----------+----------+--------+
| Samantha  | Beck      | New York |     44 |
| Clara     | Wakefield | DC       |     24 |
+-----------+-----------+----------+--------+

Example

The following example uses the Orders table:

DESCRIBE Orders;

+--------------+-------------+------+------+---------+-------+
| Field        | Type        | Null | Key  | Default | Extra |
+--------------+-------------+------+------+---------+-------+
| OrderNumber  | int(11)     | NO   | PRI  | NULL    |       |
| Status       | varchar(20) | NO   |      | NULL    |       |
| CustomerName | char(20)    | YES  |      | NULL    |       |
+--------------+-------------+------+------+---------+-------+

SELECT * FROM Orders;

+-------------+------------+--------------+
| OrderNumber | Status     | CustomerName |
+-------------+------------+--------------+
|           1 | Delivered  | John         |
|           3 | In Transit | Bon          |
|           2 | Delivered  | Kerry        |
|           4 | Delivered  | Tom          |
+-------------+------------+--------------+

The following transaction locks the row where OrderNumber is 3, using the FOR UPDATE clause. The row will remain locked as long as the transaction is open.

BEGIN;
SELECT * FROM Orders WHERE OrderNumber = 3 FOR UPDATE;

+-------------+------------+--------------+
| OrderNumber | Status     | CustomerName |
+-------------+------------+--------------+
|           3 | In Transit | Bon          |
+-------------+------------+--------------+

Now, execute the following query in a different connection:

UPDATE Orders SET Status = "Delivered" WHERE OrderNumber=3;

ERROR 1205 (HY000): Leaf Error (127.0.0.1:3307): Lock wait timeout exceeded; try restarting transaction.  Lock owned by connection id 77, query `open idle transaction`

The above query returns an error since the rows are locked by the previous transaction. To remove the lock on the rows, commit or rollback the open transaction. See the Query Errors topic for more information on locked rows.

Syntax

SingleStore supports the following JOIN and subquery syntax for the table_reference part of SELECT statements:

join_table:
           table_reference {LEFT | RIGHT | FULL} [OUTER] JOIN table_factor join_condition
         | table_reference [INNER | CROSS] JOIN table_factor [join_condition]
         | table_reference NATURAL {LEFT | RIGHT} [OUTER] JOIN table_factor
         | table_reference STRAIGHT_JOIN table_factor [join_condition]
         | table_reference, table_factor
         | table_reference LEFT [OUTER] JOIN LATERAL (subquery) [[AS] alias] join_condition
         | table_reference [INNER | CROSS] JOIN LATERAL (subquery) [[AS] alias] [join_condition]
         | table_reference, LATERAL (subquery) [[AS] alias]


join_condition:
         ON conditional_expr

Remarks

• This command must be run on the master aggregator or a child aggregator node (see Cluster Management Commands)

• STRAIGHT_JOIN forces tables to be joined in the order in which they are listed in the FROM clause

• FULL OUTER JOIN requires the join condition to be an equality.

• SingleStore supports joining tables across databases:

  SQL

  Copy

  CREATE DATABASE test1;
  USE test1;
  CREATE TABLE t1(id INT, col1 VARCHAR(10));
  
  CREATE DATABASE test2;
  USE test2;
  CREATE TABLE t2(id INT, col1 VARCHAR(10));
  
  SELECT test1.t1.*, test2.t2.* FROM test1.t1 JOIN test2.t2 ON t1.id = t2.id;

Examples

SELECT * FROM my_MemSQL_table WHERE col = 1;


SELECT COUNT(*), user_name, page_url from clicks, users, pages
 ->     WHERE clicks.user_id = users.user_id AND pages.page_id = clicks.page_id
 ->     GROUP BY users.user_id, pages.page_id
 ->     ORDER BY COUNT(*) DESC;
 +- ---------+- ----------+- -------------------------------+
 | COUNT(*) | user_name | page_url                       |
 +- ---------+- ----------+- -------------------------------+
 |        5 | jake      | memsql.com                     |
 |        2 | john      | http://www.singlestore.com/download |
 |        1 | jake      | docs.singlestore.com                |
 |        1 | jake      | memsql.com                     |
 |        1 | jake      | http://www.singlestore.com/download |
 +- ---------+- ----------+- -------------------------------+
 5 rows in set (0.00 sec)

SELECT t1.*, t2.* FROM t1 FULL OUTER JOIN t2 ON t1.a = t2.a;

Remarks

• For scalar sub-selects, sub-select queries must not return more than one row.

• The maximum allowed depth of nested sub-select queries is 40.

• Sub-selects are not supported inside GROUP BY/ORDER BY/HAVING clauses, for nested sub-select queries of depth > 2.

Examples

The following examples show the use of nested sub-selects.

SELECT cust_id FROM customers
WHERE EXISTS
  ( SELECT order_id FROM orders
    WHERE order_id IN
      ( SELECT id FROM transaction WHERE count > 5)
  );

DELETE FROM records
WHERE
  id = ( SELECT order_id
         FROM orders
         WHERE order_date > ( SELECT CURRENT_DATE() + 30)
       );

Syntax

Lateral joins use the same syntax as joins with the LATERAL keyword inserted before the lateral derived table as shown in the queries above. The syntax for lateral join is below.

join_table:
          table_reference LEFT [OUTER] JOIN LATERAL (subquery) [[AS] alias] join_condition
         | table_reference [INNER | CROSS] JOIN LATERAL (subquery) [[AS] alias] [join_condition]
         | table_reference, LATERAL (subquery) [[AS] alias]

join_condition:
         ON conditional_expr

The (subquery) [[AS] alias] clause is referred to as the lateral derived table.

Remarks

The following are restrictions on lateral join:

• Lateral derived tables can appear only in FROM clauses. A comma-separated list of tables or a join specification with JOIN, INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN are supported.

• The correlating condition can appear only in the WHERE clause of the lateral derived table.

• The correlating condition must be an equality condition (uses =).

• The correlating condition may use AND, but may not use OR.

• Lateral derived tables may not use GROUP BY.

• If a lateral derived table references an aggregate function, the function's aggregation query cannot be the one that owns the FROM clause in which the lateral derived table occurs.

• If the lateral derived table has an ORDER BY … LIMIT clause, then the LIMIT must be >0.

• TABLE functions are implicitly executed as a lateral join. The LATERAL keyword is not allowed before TABLE functions.

Examples

These examples use a table of artists and a table of albums by those artists. For simplicity we assume that each album is created by a single artist.

The statements below create artists and albums tables and insert data into those tables. Albums are linked to artists through the artist_id. Sales are in units of millions of dollars and the data is courtesy of List of best-selling albums - Wikipedia.

CREATE TABLE artists(id INT, name TEXT);

CREATE TABLE albums(album_id INT, title TEXT, 
                    genre ENUM("R&B", "Soft Rock", "Jazz"),  
                    artist_id INT, sales DOUBLE);

INSERT INTO artists VALUES
(1, "Fleetwood Mac"), 
(2, "Whitney Huston"),
(3, "The Eagles"),
(4, "Norah Jones"),
(5, "ABBA");

INSERT INTO albums VALUES 
(1, "The Bodyguard", "R&B", 2, 45),
(2, "Their Greatest Hits (1971-1975)", "Soft Rock", 3, 44),
(3, "Hotel California", "Soft Rock", 3, 42),
(4, "Rumours", "Soft Rock", 1, 40), 
(5, "Come Away with Me", "Jazz", 4, 28);

SELECT AR.name, AL1.max_sales
FROM artists AR, 
LATERAL (SELECT MAX(sales) as max_sales
          FROM albums WHERE artist_id = AR.id) AS AL1 
ORDER BY AL1.max_sales DESC;

+----------------+-----------+
| name           | max_sales |
+----------------+-----------+
| Whitney Huston |        45 |
| The Eagles     |        44 |
| Fleetwood Mac  |        40 |
| Norah Jones    |        28 |
+----------------+-----------+

SELECT AR.name, AL1.max_sales
FROM artists AR 
JOIN LATERAL (SELECT MAX(sales) as max_sales
              FROM albums 
              WHERE artist_id = AR.id) AS AL1
ORDER BY AL1.max_sales DESC;

+----------------+-----------+
| name           | max_sales |
+----------------+-----------+
| Whitney Huston |        45 |
| The Eagles     |        44 |
| Fleetwood Mac  |        40 |
| Norah Jones    |        28 |
+----------------+-----------+

SELECT name, MAX(sales)
FROM artists JOIN albums ON artists.id = albums.artist_id 
GROUP BY artists.id
ORDER BY sales DESC;

+----------------+------------+
| name           | MAX(sales) |
+----------------+------------+
| Whitney Huston |         45 |
| The Eagles     |         44 |
| Fleetwood Mac  |         40 |
| Norah Jones    |         28 |
+----------------+------------+

SELECT AR.name, AL1.max_sales
FROM artists AR 
LEFT JOIN LATERAL (SELECT sales, MAX(sales) as max_sales
          FROM albums WHERE artist_id = AR.id) AS AL1 ON TRUE
ORDER BY AL1.max_sales DESC;

+----------------+-----------+
| name           | max_sales |
+----------------+-----------+
| Whitney Huston |        45 |
| The Eagles     |        44 |
| Fleetwood Mac  |        40 |
| Norah Jones    |        28 |
| ABBA           |      NULL |
+----------------+-----------+

SELECT AR.name AS artist, AL2.title, AL1.max_sales
FROM artists AR
 JOIN LATERAL (SELECT MAX(sales) as max_sales
          FROM albums 
          WHERE artist_id = AR.id) AS AL1 ON TRUE
 JOIN LATERAL (SELECT title 
          FROM albums 
          WHERE artist_id = AR.id 
          AND sales = AL1.max_sales) AS AL2 ON TRUE
ORDER BY AL1.max_sales DESC;

+----------------+---------------------------------+-----------+
| artist         | title                           | max_sales |
+----------------+---------------------------------+-----------+
| Whitney Huston | The Bodyguard                   |        45 |
| The Eagles     | Their Greatest Hits (1971-1975) |        44 |
| Fleetwood Mac  | Rumours                         |        40 |
| Norah Jones    | Come Away with Me               |        28 |
+----------------+---------------------------------+-----------+

EXPLAIN DUPLICATE
SELECT AR.name AS artist, AL2.title, AL1.max_sales
FROM artists AR
 JOIN LATERAL (SELECT MAX(sales) as max_sales
          FROM albums 
          WHERE artist_id = AR.id) AS AL1 ON TRUE
 JOIN LATERAL (SELECT title 
          FROM albums 
          WHERE artist_id = AR.id 
          AND sales = AL1.max_sales) AS AL2 ON TRUE
ORDER BY AL1.max_sales DESC;

SELECT `artists_4`.`name` AS `artist`, `SUBQ_VWW_2_6`.`title` AS `title`, 
       `SUBQ_VWW_1_5`.`max_sales` AS `max_sales` 
FROM (( ( 
  SELECT WITH(NO_MERGE_THIS_SELECT=1) `artists_4_0`.`name` AS `name`,     
        `artists_4_0`.`id` AS `id` 
  FROM  `test`.`artists` as `artists_4_0`  ) AS `artists_4` 
  JOIN  (
    SELECT WITH(NO_MERGE_THIS_SELECT=1) `SUBQ_VWW_1_5_0`.
      `max_sales` AS `max_sales`, `SUBQ_VWW_1_5_0`.`artist_id` AS `artist_id` 
    FROM  ( 
      SELECT `albums_2`.`artist_id` AS `artist_id`, 
             MAX(`albums_2`.`sales`) AS `max_sales` 
      FROM  `test`.`albums` as `albums_2`  GROUP BY 1 ) AS `SUBQ_VWW_1_5_0` ) 
                                                    AS `SUBQ_VWW_1_5`) 
   JOIN  ( 
    SELECT WITH(NO_MERGE_THIS_SELECT=1) `SUBQ_VWW_2_6_0`.`title` AS `title`,
       `SUBQ_VWW_2_6_0`.`artist_id` AS `artist_id`, 
       `SUBQ_VWW_2_6_0`.`sales` AS `sales` 
    FROM  `test`.`albums` as `SUBQ_VWW_2_6_0`  ) AS `SUBQ_VWW_2_6`) 
WHERE ((`SUBQ_VWW_2_6`.`artist_id` = `artists_4`.`id`) 
 AND (`SUBQ_VWW_2_6`.`sales` = `SUBQ_VWW_1_5`.`max_sales`) 
 AND (`SUBQ_VWW_1_5`.`artist_id` = `artists_4`.`id`) 
 AND (`SUBQ_VWW_1_5`.`artist_id` = `SUBQ_VWW_2_6`.`artist_id`)) 
ORDER BY 3 DESC

SELECT genre, MAX(sales)
FROM 	albums
GROUP BY genre;

+-----------+------------+
| genre     | MAX(sales) |
+-----------+------------+
| R&B       |         45 |
| Jazz      |         28 |
| Soft Rock |         44 |
+-----------+------------+

SELECT genre, title, MAX(sales)
FROM 	albums
GROUP BY genre;

SET SQL_MODE = ANSI;

SELECT genre, title, MAX(sales)
FROM 	albums
GROUP BY genre;

ERROR 2295 (HY000): Column 'albums.title' from project list 
is used outside an aggregate function and does not appear in 
the GROUP BY clause. This is illegal because there may be 
multiple possible values for the field. If it is in fact 
uniquely determined, or if you wish to use one of the many 
possible values, you can use the ANY_VALUE aggregate function.

SELECT genre, title, MAX(sales)
FROM 	albums
GROUP BY genre;

SHOW WARNINGS;

Warning | 2295 | Column 'albums.title' from project list is used 
outside an aggregate function and does not appear in the GROUP BY 
clause. This is illegal because there may be multiple possible values 
for the field. If it is in fact uniquely determined, or if you wish to 
use one of the many possible values, you can use the ANY_VALUE aggregate 
function.

SELECT AL2.genre, AL2.title, AL1.max_sales
FROM albums AL
 JOIN LATERAL (SELECT MAX(sales) as max_sales
          FROM albums 
          WHERE genre = AL.genre) AS AL1 ON TRUE
 JOIN LATERAL (SELECT title, genre 
          FROM albums 
          WHERE title = AL.title 
          AND genre = AL.genre
          AND sales = AL1.max_sales) AS AL2 ON TRUE
ORDER BY AL1.max_sales DESC;

+-----------+---------------------------------+-----------+
| genre     | title                           | max_sales |
+-----------+---------------------------------+-----------+
| R&B       | The Bodyguard                   |        45 |
| Soft Rock | Their Greatest Hits (1971-1975) |        44 |
| Jazz      | Come Away with Me               |        28 |
+-----------+---------------------------------+-----------+

SELECT * FROM t, TABLE(JSON_TO_ARRAY('[1,2,3]'));

SELECT * FROM t, LATERAL TABLE(JSON_TO_ARRAY('[1,2,3]'));

SELECT artists.name, AL1.max_sales
FROM artists 
   RIGHT JOIN LATERAL (SELECT sales, MAX(sales) as max_sales
          FROM albums WHERE artist_id = artists.id) AS AL1 ON TRUE
ORDER BY AL1.max_sales DESC;

ERROR 1054 (42S22): Unknown column 'artists' in 'previous table'

SELECT AR.name AS artist, AL2.max_sales
FROM artists AR
 JOIN LATERAL 
    (SELECT FORMAT(AL1.max_sales_in_mil*1000000,0) as max_sales
     FROM
       (SELECT MAX(sales) as max_sales_in_mil
          FROM albums 
          WHERE artist_id = AR.id) AS AL1
    ) AS AL2 ON TRUE
ORDER BY AL2.max_sales DESC;

+----------------+------------+
| artist         | max_sales  |
+----------------+------------+
| Whitney Huston | 45,000,000 |
| The Eagles     | 44,000,000 |
| Fleetwood Mac  | 40,000,000 |
| Norah Jones    | 28,000,000 |
+----------------+------------+

Syntax

Using a JOIN clause with the USING clause will match only one column when there are more than one columns that match.

SELECT <table1_name>.<column_name> AS <column_name> 
    FROM <table_name> JOIN <table_name> USING (<column_name>);

Remarks

• You can utilize the USING clause instead of the ON clause in JOIN operations with an explicit JOIN clause.

Examples

TABLE 1:

CREATE TABLE assets(
asset_id int,
asset_type varchar(50),
asset_desc varchar(50),
emp_id numeric (5)
);

TABLE 2:

CREATE TABLE
employees(
emp_id numeric (5),
emp_name varchar(75)
);

SELECT employees.emp_name AS employees, asset_desc as assets, assets.asset_type
    FROM assets JOIN employees USING (emp_id);

+------------------------------------+
|employees |assets      |asset_type  |
+------------------------------------+
|T_Willams |macbook pro | laptop     |
+------------------------------------+
|A_Young   |iphone 12   | cell phone |
+------------------------------------+
|D_Karras  |macbook air | laptop     |
+------------------------------------+

Remarks

• The SELECT ... INTO statement must return only a single result row.

• The number of columns/expressions in the SELECT query must be the same as the number of variables being initialized in the INTO list.

• SELECT ... INTO variable statements must be used inside PSQL procedure blocks.

• The INTO variable clause can only be used once inside a SELECT query.

• A SELECT ... INTO statement cannot be used inside of a sub-select query.

• The variables in SELECT ... INTO statements must be declared with scalar data types.

• SELECT ... INTO variable and SELECT ... INTO OUTFILE/S3/KAFKA cannot be used in the same query.

• If the SELECT ... INTO statement returns 0 rows, SingleStore throws an error: ER_INTO_VARIABLES_NO_ROWS. To accommodate for this error, you can specify an EXCEPTION in the DECLARE block of a stored procedure (see Example 3 below).

Examples

Note: The following examples use the hrRec table.

DESC hrRec;

+-----------+-------------+------+------+---------+-------+
| Field     | Type        | Null | Key  | Default | Extra |
+-----------+-------------+------+------+---------+-------+
| FirstName | varchar(20) | YES  |      | NULL    |       |
| LastName  | varchar(20) | YES  |      | NULL    |       |
| City      | varchar(20) | YES  |      | NULL    |       |
| Tenure    | int(11)     | YES  |      | NULL    |       |
+-----------+-------------+------+------+---------+-------+

DELIMITER //

CREATE OR REPLACE PROCEDURE into_var ()
AS
DECLARE
fname VARCHAR(20); lname VARCHAR(20); city VARCHAR(20); ten INT;
BEGIN
SELECT FirstName, LastName, City, Tenure
 INTO fname, lname, city, ten
FROM hrRec
WHERE Tenure > 40;
ECHO SELECT CONCAT(fname, " ", lname) AS "Name", city AS "City", ten AS "Tenure";
END //

DELIMITER ;

CALL into_var();

+---------------+----------+--------+
| Name          | City     | Tenure |
+---------------+----------+--------+
| Samantha Beck | New York |     44 |
+---------------+----------+--------+

DELIMITER //

CREATE OR REPLACE PROCEDURE into_var () AS
DECLARE
  row_c INT; sum_t INT;
BEGIN
  SELECT COUNT(*), SUM(Tenure) INTO row_c, sum_t FROM hrRec;
  ECHO SELECT row_c AS "Row Count", sum_t AS "Tenure";
END //

DELIMITER ;

CALL into_var();

+-----------+--------+
| Row Count | Tenure |
+-----------+--------+
|         5 |    170 |
+-----------+--------+

DELIMITER //

CREATE OR REPLACE PROCEDURE into_var () AS
DECLARE
  row_c INT; sum_t INT; qry VARCHAR(50);
BEGIN
  qry = "SELECT COUNT(*), SUM(Tenure) FROM hrRec";
  EXECUTE IMMEDIATE qry INTO row_c, sum_t;
  ECHO SELECT row_c AS "Row Count", sum_t AS "Tenure";
END //

DELIMITER ;

CALL into_var();

+-----------+--------+
| Row Count | Tenure |
+-----------+--------+
|         5 |    170 |
+-----------+--------+

DELIMITER //

CREATE OR REPLACE PROCEDURE cityCount () AS
DECLARE cnum INT;
BEGIN
  SELECT City INTO cnum FROM hrRec WHERE Tenure > 50;
  ECHO SELECT cnum;
END //

DELIMITER ;

CALL cityCount();

ERROR 2439 (HY000): Unhandled exception
Type: ER_INTO_VARIABLES_NO_ROWS (2439)
Message: Query with 'INTO VARIABLES' clause returned zero rows whereas expected 1 row

DELIMITER //

CREATE OR REPLACE PROCEDURE cityCount () AS
DECLARE cnum INT;
BEGIN
  SELECT City INTO cnum FROM hrRec WHERE Tenure > 50;
  ECHO SELECT cnum;
EXCEPTION
  WHEN ER_INTO_VARIABLES_NO_ROWS THEN
  cnum = 'No city found!';
  ECHO SELECT cnum;
END //

DELIMITER ;

CALL cityCount();

+----------------+
| cnum           |
+----------------+
| No city found! |
+----------------+

EXCEPTION
  WHEN ER_SUBQUERY_NO_1_ROW THEN
  ECHO SELECT 'SELECT ... INTO statement returned more than 1 row.';

Syntax

SELECT [ALL | DISTINCT | DISTINCTROW] FROM table_reference

Remarks

• The ALL clause returns all the matching rows including duplicates.

  The DISTINCT or DISTINCTROW clause returns the matching rows but eliminates the duplicate rows from the output. DISTINCTROW is a synonym of DISTINCT.

Examples

SELECT * FROM Product;

+------------+------------+----------+
| Product_id | Brand_name | City     |
+------------+------------+----------+
|          1 | Nike       | London   |
|          3 | Nike       | New York |
|          2 | Adidas     | Paris    |
|          3 | Puma       | Spain    |
+------------+------------+----------+

SELECT ALL Brand_name FROM Product;

+------------+
| Brand_name |
+------------+
| Nike       |
| Nike       |
| Adidas     |
| Puma       |
+------------+

SELECT DISTINCT Brand_name FROM Product;

+------------+
| Brand_name |
+------------+
| Nike       |
| Adidas     |
| Puma       |
+------------+

Remarks

• If a relative path that that does not start with / is specified, SingleStore writes the file to the directory specified in the global variable datadir. To specify another location, enter the absolute path to the file as the file_name parameter.

• The default text formatting, used when the user omits the FIELDS and LINES blocks, is to separate values with tabs (\t) and rows with newlines (\n).

Example

The following query writes the contents of table_name to a file in the home directory of username. The file is formatted as a comma-separated values (CSV) file.

SELECT * FROM table_name INTO OUTFILE '/home/username/file_name.csv'
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'

Example

The following example demonstrates how to write the contents of a table to HDFS at a specified path.

SELECT * FROM stockTo
INTO HDFS 'hdfs://hadoop-namenode:8020/stock_dir/records_file.csv'

Example

The following example writes the contents of the table t1, to the S3 bucket at the specified path, using the S3 connection link S3con stored in the db1 database:

USE db1;
SELECT * FROM t1 INTO LINK S3con 'testing/output';

Note: The connection link S3Con should already exist in db1. Refer to the CREATE LINK topic for details on creating a connection link.

Remarks

• By default, SingleStore writes the files to the directory specified in the global variable basedir. To specify another location, enter the absolute path to the file as the destination_directory parameter.

• The default text formatting, used when the user omits the FIELDS and LINES blocks, is to separate values with tabs (\t) and rows with newlines (\n).

Example

The following query writes the contents of table_name to a set of files in the /tmp directory on each leaf node (/tmp/a_0, /tmp/a_1, /tmp/a_2, etc.). The files are comma-separated value (CSV) with one output file per partition (a_0 is for partition 0, a_1 is for partition 1, etc.).

SELECT * FROM table_name INTO FS '/tmp/a'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

Remarks

• The multipart_chunk_size_mb must be in the range of [5..500]. By default, the chunk size is 5 MB. A larger chunk size allows users to upload large files without going over Amazon’s limitation on maximum number of parts per upload. Although, a larger chunk size increases the chance of a network error during the upload to S3. If a chunk fails to upload, SingleStore retries uploading it until the limit set on the number of retries by AWS is reached.

  Note

  Each partition will use multipart_chunk_size_mb MB(s) of additional memory.

• The output of SELECT ... INTO S3 is stored with the content type binary/octet-stream in the S3 bucket.

• If the insert select logic determines that the SELECT ... INTO S3 query can be run as distributed, the query will be pushed down to each leaf and executed on each partition. The name of each object will be:

  Copy

  <bucket/target>_<partition ID>

• If the insert select logic determines that the SELECT ... INTO S3 query can only be on the aggregator because it contains aggregations, ORDER BY, GROUP BY, etc. then the query will be run on each leaf but the result will be collected on the aggregator and then output to S3. The object name will just be:

  Copy

  <bucket/target>

• The SELECT query will validate if the <bucket/target> or <bucket/target>_ already exists on the S3 bucket first and fail if any of the object(s) already exist.

• As an alternative to using a SELECT ... INTO S3 statement where you specify the CONFIG and CREDENTIALS clauses, you can use a SELECT ... INTO LINK statement, where you reference a connection link. For more information, see Configuring and Using Connection Links.

Examples

The following simple example shows how to save all rows in table t1 to an S3 bucket using an AWS access key.

SELECT *
FROM t1
INTO S3 'testing/output'
CONFIG '{"region":"us-east-1"}'
CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}'

The following example saves the result set of a SELECT query with a GROUP BY clause and sends the file in chunks to an S3 bucket using an Amazon Resource Name (ARN) for AWS Identity and Access Management (IAM).

SELECT t1.a, t2.a
FROM t1, t2
WHERE t1.a = t2.a
GROUP BY t1.a
INTO S3 'bucket_name/file_name'
CONFIG '{"region":"us-east-1", "multipart_chunk_size_mb":100}'
CREDENTIALS '{"role_arn": "arn:aws:iam::<AccountID>:role/EC2AmazonS3FullAccess"}'

The following example uses the format options to output the data in CSV format.

SELECT *
FROM t
INTO S3 'tmp/a'
CONFIG '{"region":"us-east-1"}'
CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}'
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

Remarks

• Unlike other filesystem options, with SELECT ... INTO GCS, data written by a single partition is not divided into chunks. However, there may still be multiple partitions and thus multiple files (one per partition) will appear in GCS.

• The maximum object size supported by GCS is 5 TB; this limit also applies to SELECT ... INTO GCS.

• The CONFIG clause may optionally specify an endpoint_url.

• The CREDENTIALS clause is required.

• We support only HMAC keys.

• The CREDENTIALS clause should be a JSON object with two fields:

access_id: usually a 24 or 60 character alphanumeric string, which is linked to the Google account, typically all uppercase and starts with GOOG.

secret_key: usually a 40 character Base-64 encoded string that is linked to a specific access_id.

As an alternative to using a SELECT ... INTO GCS statement where you specify the CONFIG and CREDENTIALS clauses, you can use a SELECT ... INTO LINK statement, where you reference a connection link. For more information, see Configuring and Using Connection Links.

Examples

The following simple example shows how to save all rows in table table_name to a GCS bucket using a Google access key, and outputs them as a CSV.

CREATE TABLE table_name (column_name INT);
INSERT INTO table_name VALUES (1), (2), (3);
SELECT *
FROM table_name
INTO GCS 'bucket/path'
CREDENTIALS '{"access_id": "replace_with_your_google_access_key", "secret_key": "replace_with_your_google_secret_key"}' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Arguments

Remarks

• When possible, SingleStore queries leaf nodes directly, bypassing the aggregator. This allows SingleStore to send data directly from the leaves to Kafka partitions.

• SingleStore constructs a Kafka message as an array of bytes.

• As an alternative to using a SELECT ... INTO KAFKA statement where you specify the CONFIG and CREDENTIALS clauses, you can use a SELECT ... INTO LINK statement, where you reference a connection link. For more information, see Configuring and Using Connection Links.

Examples

SELECT col1, col2, col3 FROM t
ORDER BY col1
INTO KAFKA 'host.example.com:9092/test-topic'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY "\t"
LINES TERMINATED BY '}' STARTING BY '{';

+------+------+------+
| col1 | col2 | col3 |
+------+------+------+
| a    | b    | c    |
| d    | e    | f    |
| g    | h\ti | j    |
+------+------+------+

SELECT col1, col2, col3 FROM t
ORDER BY col1
INTO KAFKA 'host.example.com:9092/test-topic'
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY '';

SELECT text FROM t INTO
KAFKA 'host.example.com:9092/test-topic'
CONFIG '{"security.protocol": "ssl",
"ssl.certificate.location": "/var/private/ssl/client_memsql_client.pem",
"ssl.key.location": "/var/private/ssl/client_memsql_client.key",
"ssl.ca.location": "/var/private/ssl/ca-cert.pem"}'
CREDENTIALS '{"ssl.key.password": "abcdefgh"}'

SELECT text FROM t INTO
KAFKA 'host.example.com:9092/test-topic'

SELECT text FROM t INTO
KAFKA 'host1.example.com:9092,host2.example.com:9092,host3.example.com:9092/test-topic'

SELECT column_b, column_a, column_b, column_c FROM kktest INTO 
KAFKA 'localhost:1234/kafkaWithKey' KAFKA KEY "column_b";

Syntax

FORMAT PARQUET

Remarks

• The FORMAT PARQUET clause will go at the end of the SELECT statement.

• Time and date types are stored in the engine timezone without adjusting to UTC. Below are some examples of the differences between SQL and Parquet time and date formats. If any value of SQL TIME(6) is outside the range of the Parquet TIME, the following error message may occur:

  Copy

  can not save SQL TIME(6) … because parquet's TIME is within a single day
  (hour should be between 0 and 23); consider casting this column to BIGINT

• If any value of TIMESTAMP is zero, which is outside the range of the Parquet TIMESTAMP, the following error message may occur:

  Copy

  can not be saved as TIMESTAMP with millisecond precision because 
  zero value is outside the scope of the parquet type; consider casting 
  this column to INT64

Examples

The examples below have been shortened to illustrate the use of the Parquet format. The data ingestion, creation of table2, and start pipeline are not shown.

CREATE TABLE table1 (f_CHAR_2_5 CHAR(2), f_LONGTEXT4 LONGTEXT NOT NULL, 
f_DATETIME_6_3 DATETIME(6), f_NUMERIC_27_13__UNSIGNED2 NUMERIC(27,13) UNSIGNED, f_TINYINT1 TINYINT);

SELECT * FROM table1 into fs ‘/tmp/parquet_files’ FORMAT PARQUET;

CREATE PIPELINE pipe1 AS LOAD DATA fs ‘/tmp/parquet_files’ 
INTO TABLE table2 FORMAT PARQUET (f_CHAR_2_5 <- %::f_CHAR_2_5, f_LONGTEXT4 <- %::f_LONGTEXT4, 
@f_DATETIME_6_3 <- %::f_DATETIME_6_3, f_NUMERIC_27_13__UNSIGNED2 <- %::f_NUMERIC_27_13__UNSIGNED2, 
f_TINYINT1 <- %::f_TINYINT1 )  SET f_DATETIME_6_3=DATE_ADD('1970-01-01', INTERVAL @f_DATETIME_6_3 MICROSECOND);

CREATE TABLE table1 (f_REAL3 REAL NOT NULL, f_TIME2 TIME, f_ENUM__small___medium___large__1 ENUM('small','medium','large'));

SELECT * FROM table1 into fs '/tmp/parquet_files' FORMAT PARQUET;

CREATE  PIPELINE pipe2 AS LOAD DATA fs '/tmp/parquet_files' INTO TABLE table2 FORMAT PARQUET 
(f_REAL3 <- %::f_REAL3, @f_TIME2 <- %::f_TIME2, f_ENUM__small___medium___large__1 <- %::f_ENUM__small___medium___large__1 )
SET f_TIME2=sec_to_time(@f_TIME2/1000);

CREATE ROWSTORE TABLE table1 (f_TEXT5 TEXT NOT NULL, 
f_GEOGRAPHY4 GEOGRAPHY NOT NULL, f_VARCHAR_200_3 VARCHAR(200), 
f_DECIMAL_27_13_2 DECIMAL(27,13), f_INT_UNSIGNED1 INT UNSIGNED);

SELECT * FROM table1 INTO OUTFILE '/tmp/parquet_files3' FORMAT PARQUET;

CREATE LINK p_link_20_56_14_557666 AS fs CREDENTIALS '{}' CONFIG '{}';

CREATE PIPELINE pipe3 AS LOAD DATA LINK p_link_20_56_14_557666
'/tmp/parquet_files3' INTO TABLE table2 FORMAT PARQUET (f_TEXT5 <- %::f_TEXT5,
f_GEOGRAPHY4 <- %::f_GEOGRAPHY4, f_VARCHAR_200_3 <- %::f_VARCHAR_200_3, 
f_DECIMAL_27_13_2 <- %::f_DECIMAL_27_13_2, f_INT_UNSIGNED1 <- %::f_INT_UNSIGNED1);

Argument

ratio

A decimal between 0.0 and 1.0 specifying the probability that each row will be retrieved from the table. The probability is applied to each row independently.

Remarks

• When you run multiple SELECT statements against the same table using WITH (SAMPLE_RATIO = <value>), you will receive similar results. This is the case even when your sample ratio is different in your SELECT statements. For example, the sample returned for a sample ratio of 0.25 is often a subset of the sample ratio returned for 0.5.

• Using a sample ratio that is one divided by a power of two allows WITH (SAMPLE_RATIO = <value>) to run more efficiently.

• You can use WITH (SAMPLE_RATIO = <value>) in a SELECT subquery.

• SAMPLE_RATIO is not applicable inside Common Table Expressions (CTE).

Examples

Simple SELECT statements

The following example populates a table with the integers 1 to 10 and selects two random samples.

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE example_table(num INT);

INSERT INTO example_table VALUES (5), (6), (10), (11), (12),
  (14), (20), (24), (33), (40);

The query below returns a random sample of the num column. Each row in the sample has a seventy percent probability of being returned.

SELECT num FROM example_table WITH (SAMPLE_RATIO = 0.7) ORDER BY num;

Example output follows. You will likely see different results, as the query returns a random sample.

+------+
| num  |
+------+
|    6 |
|   11 |
|   14 |
|   20 |
|   24 |
|   33 |
+------+

The query below returns the average of a random sample of the num column. Each row in the sample has an eighty percent probability being included in the sample and therefore in the AVG computation.

SELECT AVG(num) FROM example_table WITH (SAMPLE_RATIO = 0.8);

Example output follows. You will likely see different results, as the query returns a random sample.

+----------+
| AVG(num) |
+----------+
|  18.1111 |
+----------+

Using WITH (SAMPLE_RATIO = <value>) with SELECT Subqueries

You may use WITH (SAMPLE_RATIO = <value>)as part of a SELECT subquery, as shown in the following example.

CREATE TABLE example_table_2 AS SELECT * FROM example_table
  WITH (SAMPLE_RATIO = 0.6);
SELECT num FROM example_table2 ORDER BY num;

Example output follows. You will likely see different results, as the query returns a random sample.

+------+
| num  |
+------+
|   10 |
|   12 |
|   14 |
|   24 |
|   33 |
|   40 |
+------+

Using WITH (SAMPLE_RATIO = <value>) with Joins

You may use WITH (SAMPLE_RATIO = <value>) in a SELECT statement that contains a join. However, you should do so carefully to ensure the join yields the results you intend. For example, consider joining a CUSTOMER table to an ORDER table using the customer_id field in the ORDER table. The placement of the WITH (SAMPLE_RATIO = <value>) clause within the join will greatly impact the result, as shown below.

The following query selects a sample of customers and returns the orders for those customers only.

SELECT c.name, o.order_id, o.order_total
FROM customer WITH (SAMPLE_RATIO = 0.4) c
JOIN order o
ON c.customer_id = o.customer_id