SingleStore Helios

https://docs.singlestore.com
Admin
SingleStore Helios is a distributed SQL database built for intelligent applications, combining...
Tokens:4,037,435
Snippets:30,585
Trust Score:10
Update:1 day ago
Show doc for...
Tokens:
Raw
### Setup table for SAMPLE_RATIO examples

Source: https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/select/

Creates a database and table, and inserts integers 1 to 10 for the SAMPLE_RATIO examples.

```sql
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);
```

--------------------------------

### Start singlestoredb-studio service (package-based)

Source: https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/singlestore-studio/singlestore-studio-installation/

Starts the singlestoredb-studio service using systemd. Use this after installing the package.

```bash
sudo systemctl start singlestoredb-studio
```

--------------------------------

### sudo apt-get install example

Source: https://docs.singlestore.com/db/v9.1/deploy/linux/cli-offline-deb/

Example of installing packages with sudo privileges, which is required for the deployment user to install packages on all hosts.

```bash
sudo apt-get install ...
```

--------------------------------

### Start Studio with nohup for tarball installations

Source: https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/singlestore-studio/singlestore-studio-websocket-proxy/

Start SingleStore Studio in the background using nohup for non-sudo/tarball-based installations.

```bash
nohup ./singlestoredb-studio > studio.stdout 2> studio.stderr < /dev/null &
```

--------------------------------

### Create database schema and populate sample data

Source: https://docs.singlestore.com/cloud/reference/data-api/data-api-endpoint-reference/query-rows/

Optional setup to create the 'publications' database and tables, and insert sample data for the examples. Run this before the query examples to emulate the responses.

```sql
CREATE DATABASE publications;
USE publications;
DROP TABLE IF EXISTS books;
CREATE TABLE books (book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, description VARCHAR(255), author_id INT NOT NULL, genre VARCHAR (20) NOT NULL, rating JSON NOT NULL, published_date DATE, standard_number CHAR(11) NOT NULL, KEY (title) USING HASH);
DROP TABLE IF EXISTS authors;
CREATE TABLE authors(author_id INT AUTO_INCREMENT PRIMARY KEY, author_name VARCHAR(50) NOT NULL, bio VARCHAR(255));
INSERT INTO books (title, author_id, genre, rating, standard_number) VALUES ('Dangerous Spindle', 3, 'Crime Thriller', '{"Noveliu" : "4.5/5", "AllBooksWeb" : "4.4/5"}', '728-2397-903-3'), ('The Owl in the Abyss', 1, 'Fantasy', '{"Noveliu" : "4/5", "AllBooksWeb" : "4.2/5"}', '738-0831-223-8'), ('Judged for Error', 2, 'Historical Fiction', '{"Noveliu" : "4.5/5", "AllBooksWeb" : "4.5/5"}', '257-2684-239-3');
INSERT INTO publications.authors (author_name) VALUES ('Kegan Roslyn'), ('Toby Lynsey'), ('Lacey Desmond');
```

--------------------------------

### Example RPM/Debian configuration file (singlestoredb-studio.hcl)

Source: https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/singlestore-studio/singlestore-studio-architecture/

Example configuration file for RPM/Debian installations. Sets host, port, statePath, and logPath. The state and log file locations can be overridden here.

```hcl
# This is the IP address that Studio will bind to.
host = "0.0.0.0"

# This is the port that Studio will bind to.
port = 8080

# This is the default path to the Studio state file.
statePath = "/var/lib/singlestoredb-studio/studio.hcl"

# This is the default path to the Studio log file.
logPath = "/var/lib/singlestoredb-studio/studio.log"

```

--------------------------------

### BEGIN example - start transaction

Source: https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/begin/

Starts a new transaction. This is the first step in the example workflow.

```sql
BEGIN;
```

--------------------------------

### Run sdb-deploy setup-cluster with example.yaml

Source: https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/sdb-deploy-commands/setup-cluster/

Command to run sdb-deploy setup-cluster using a cluster file named example.yaml. The cluster file must be created from the provided examples.

```bash
sdb-deploy setup-cluster --cluster-file example.yaml
```

--------------------------------

### Start Postfix service

Source: https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/cluster-health-and-performance/configure-alerts/

Starts the Postfix service after installation on Red Hat.

```bash
systemctl start postfix
```

--------------------------------

### CREATE TABLE set_example and INSERT

Source: https://docs.singlestore.com/cloud/reference/sql-reference/user-defined-variables/set/

Creates a table and inserts sample data for use in subsequent examples.

```sql
CREATE TABLE set_example (a int, b int);
INSERT INTO set_example VALUES(1,2),(3,4),(5,6),(7,8);

SELECT * FROM set_example;
```

```output
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
+------+------+
4 rows in set (0.001 sec)
```

--------------------------------

### Start x86_64 shell

Source: https://docs.singlestore.com/cloud/developer-resources/connect-with-application-development-tools/connect-with-odbc/the-singlestore-odbc-driver/

Starts a shell in x86 mode to install Homebrew for x86_64.

```bash
arch -x86_64 zsh
```

--------------------------------

### Create and populate sample tables

Source: https://docs.singlestore.com/cloud/query-data/basic-query-examples/

Sets up the sample database and tables (departments, employees, salaries) and populates them with small datasets. Run these queries after connecting to SingleStore.

```sql
-- Create the database

CREATE DATABASE memsql_example;
USE memsql_example;

-- Create 3 tables: departments, employees, and salaries

CREATE TABLE departments (
  id int,
  name varchar(255),
  PRIMARY KEY (id)
);

CREATE TABLE employees (
  id int,
  deptId int,
  managerId int,
  name varchar(255),
  hireDate date,
  state char(2),
  PRIMARY KEY (id)
);

CREATE TABLE salaries (
  employeeId int,
  salary int,
  PRIMARY KEY (employeeId)
);

-- Populate each table with data

INSERT INTO departments (id, name) VALUES
  (1, 'Marketing'), (2, 'Finance'), (3, 'Sales'), (4, 'Customer Service');

INSERT INTO employees (id, deptId, managerId, name, hireDate, state) VALUES
  (1, 2, NULL, "Karly Steele", "2011-08-25", "NY"),
  (2, 1, 1, "Rhona Nichols", "2008-09-11", "TX"),
  (3, 4, 2, "Hedda Kent", "2005-10-27", "TX"),
  (4, 2, 1, "Orli Strong", "2001-07-01", "NY"),
  (5, 1, 1, "Leonard Haynes", "2011-05-30", "MS"),
  (6, 1, 5, "Colette Payne", "2002-10-22", "MS"),
  (7, 3, 4, "Cooper Hatfield", "2010-08-19", "NY"),
  (8, 2, 4, "Timothy Battle", "2001-01-21", "NY"),
  (9, 3, 1, "Doris Munoz", "2008-10-22", "NY"),
  (10, 4, 2, "Alea Wiggins", "2007-08-21", "TX");

INSERT INTO salaries (employeeId, salary) VALUES
  (1, 885219), (2, 451519), (3, 288905), (4, 904312), (5, 919124),
  (6, 101538), (7, 355077), (8, 900436), (9, 41557), (10, 556263);
```

--------------------------------

### SHOW CREATE VIEW example with output

Source: https://docs.singlestore.com/cloud/reference/sql-reference/show-commands/show-create-view/

Example command and its output for the shipped_products view. The output includes the Create View field which can be used to recreate the view.

```sql
SHOW CREATE VIEW shipped_products\G
```

```text
*** 1. row ***
                View: shipped_products
         Create View: CREATE DEFINER=`root`@`%` SCHEMA_BINDING=OFF VIEW `shipped_products` AS SELECT `orders`.`product_id` AS `product_id`, `orders`.`quantity` AS `quantity` FROM `orders` as `orders`  WHERE (NOT ISNULL(`orders`.`quantity`))
character_set_client: utf8
collation_connection: utf8mb4_bin
```

--------------------------------

### Run vee-fs installer

Source: https://docs.singlestore.com/db/v9.1/security/encryption/securing-data-at-rest-with-ciphertrust-transparent-encryption/setting-up-cte-components-and-a-singlestore-cluster/

Execute the vee-fs binary to start the installation and configuration prompts.

```bash
./vee-fs-6.3.1-74-ubuntu18-x86_64.bin
```

--------------------------------

### Start Apache NiFi

Source: https://docs.singlestore.com/cloud/load-data/integrate-with-singlestore-helios/connect-with-apache-nifi/

Run this command from the NiFi installation directory to start Apache NiFi.

```bash
./bin/nifi.sh start
```

--------------------------------

### SQL: Create database and tables for Beam example

Source: https://docs.singlestore.com/cloud/load-data/integrate-with-singlestore-helios/load-data-from-apache-beam/

Creates the database `dbExample` and tables `readExample` and `writeExample`, then inserts sample data into `readExample`. This setup is required before running the Beam read operation.

```sql
CREATE DATABASE dbExample;
USE dbExample;

CREATE TABLE readExample (
	ID INT,
	Name VARCHAR(32)
);

CREATE TABLE writeExample (
	ID INT,
	Name VARCHAR(32)
);

INSERT INTO readExample VALUES
(1, 'Adam'),
(2, 'Bill'),
(3, 'Clark'),
(4, 'Dennis'),
(5, 'Erik'),
(6, 'Finn'),
(7, 'Grace'),
(8, 'Henry');
```

--------------------------------

### SYS_GUID example: output

Source: https://docs.singlestore.com/db/v9.1/reference/sql-reference/identifier-generation-functions/sys-guid-and-uuid/

Example output from the SELECT query, showing the HEX representation of the GUID.

```text
+------+----------------------------------+
| a    | HEX(b)                           |
+------+----------------------------------+
|    1 | 7CD5B7769DF95CEFE034080020825436 |
+------+----------------------------------+
```

--------------------------------

### USE example with output

Source: https://docs.singlestore.com/cloud/reference/sql-reference/data-manipulation-language-dml/use/

Example of using the USE command to select the 'test' database. The output 'Database changed' confirms the command executed successfully.

```sql
USE test;
```

```text
Database changed
```

--------------------------------

### sdb-deploy setup-cluster basic example

Source: https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/sdb-deploy-commands/setup-cluster/

Deploys a cluster with a master aggregator, child aggregator, and leaf nodes. Requires --password and optionally --license or MEMSQL_LICENSE environment variable. Hosts must be accessible via SSH with the specified identity file.

```bash
sdb-deploy setup-cluster \
  --master-host user@host1:33 \
  --aggregator-hosts host2 \
  --leaf-hosts host3,host4 \
  --identity-file ~/my_key.pem \
  --license AAAAAAa/Aaa/AA== \
  --password testpass
```

--------------------------------

### Create and populate lead_example table

Source: https://docs.singlestore.com/cloud/reference/sql-reference/window-functions/lead/

Creates the lead_example table and inserts sample data used in the LEAD() examples.

```sql
CREATE TABLE lead_example (a int, b int);

INSERT INTO lead_example VALUES(1,2),(2,2),(3,3),(4,3);
```

--------------------------------

### LOCATE example with start position

Source: https://docs.singlestore.com/cloud/reference/sql-reference/string-functions/locate/

Returns 8, the position of 'i' in 'ohaiohai' when starting the search at position 6.

```sql
SELECT LOCATE('i', 'ohaiohai', 6);
```

```text
+----------------------------+
| LOCATE('i', 'ohaiohai', 6) |
+----------------------------+
|                          8 |
+----------------------------+
```

--------------------------------

### Install django-singlestore from GitHub

Source: https://docs.singlestore.com/cloud/developer-resources/connect-with-application-development-tools/connect-with-python/connect-with-django/

Run this command to install the django-singlestore connector from the GitHub repository.

```bash
pip install git+https://github.com/singlestore-labs/django-singlestore
```

--------------------------------

### SHOW GROUPS FOR USER example and output

Source: https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/show-groups-for-user/

Example command showing groups for user 'root'@'localhost', followed by its output table.

```sql
SHOW GROUPS FOR USER 'root'@'localhost';
```

```text
+-------------------------------+
| Groups of user root@localhost |
+-------------------------------+
| group_0                       |
+-------------------------------+
1 row in set (0.00 sec)
```

--------------------------------

### SET OFFSETS with Partition Offsets Example

Source: https://docs.singlestore.com/cloud/reference/sql-reference/pipelines-commands/alter-pipeline/

Configures the pipeline to start reading from specific source partitions and offsets. In this example, the data source has two partitions with IDs 0 and 1, and the pipeline starts reading from offset 100 in both.

```sql
ALTER PIPELINE mypipeline SET OFFSETS '{"0":100,"1":100}';
```

--------------------------------

### SHOW FUSION HELP FOR example with CREATE WORKSPACE

Source: https://docs.singlestore.com/cloud/reference/fusion-sql/show-fusion-help-for/

Example command that displays documentation for the CREATE WORKSPACE Fusion SQL command.

```sql
SHOW FUSION HELP FOR 'CREATE WORKSPACE';
```

--------------------------------

### Setup SQL for Python ECHO example

Source: https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/echo/

SQL statements to set up a database and stored procedure for the Python ECHO example.

```sql
-- Setup for Python ECHO example.
USE information_schema;
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE IF NOT EXISTS tbl(id INT, value VARCHAR(30));

INSERT INTO tbl VALUES(1, "red");
INSERT INTO tbl VALUES(2, "green");
INSERT INTO tbl VALUES(3, "blue");

DELIMITER //
CREATE OR REPLACE PROCEDURE p(x INT) RETURNS QUERY(id INT, value VARCHAR(30)) AS
DECLARE
    r QUERY(id INT, value VARCHAR(30)) =
          SELECT id, value FROM tbl
          WHERE id >= x;
BEGIN
    RETURN r;
END
//
DELIMITER ;
```

--------------------------------

### Enable singlestoredb-studio service to start on reboot (package-based)

Source: https://docs.singlestore.com/db/v9.1/reference/singlestore-tools-reference/singlestore-studio/singlestore-studio-installation/

Enables the singlestoredb-studio service to start automatically after the host is rebooted. Recommended step.

```bash
sudo systemctl enable singlestoredb-studio.service
```

--------------------------------

### Start offline Kafka broker and verify status

Source: https://docs.singlestore.com/cloud/load-data/data-sources/load-data-from-kafka/kafka-pipeline-errors/

Start a failed Kafka broker using systemctl, then verify it is running. The example starts broker2 and confirms it becomes active.

```bash
ubuntu@kafka:~/kafka/scripts$ sudo systemctl start broker2
```

```bash
ubuntu@kafka:~/kafka/scripts$ sudo systemctl status broker2 | grep Active
```

```text
Active: active (running) since Wed 2023-01-04 01:26:21 UTC; 1min 14s ago
```

--------------------------------

### SHOW FUSION COMMANDS example with LIKE

Source: https://docs.singlestore.com/cloud/reference/fusion-sql/show-fusion-commands/

Example that lists Fusion SQL commands starting with 'SHOW' using the LIKE clause.

```sql
SHOW FUSION COMMANDS LIKE 'SHOW%';
```

--------------------------------

### SHOW USERS example with output

Source: https://docs.singlestore.com/cloud/reference/sql-reference/security-management-commands/show-users/

Example of running SHOW USERS and its resulting table showing user details such as User, Type, Connections, Is deleted, Default resource pool, and Is local.

```sql
SHOW USERS;
```

```text
+-------------------------+--------+-------------+------------+-----------------------+-----------+
| User                    | Type   | Connections | Is deleted | Default resource pool | Is local  |
+-------------------------+--------+-------------+------------+-----------------------+-----------+
| 'admin'@'127.0.0.1'     | Native |           0 |            |                       | SYNC      |
| 'admin'@'localhost'     | SAML   |           0 |            |                       | SYNC      |
| 'dashboard'@'127.0.0.1' | Native |           0 |            | respool1              | SYNC      |
| 'root'@'%'              | Native |           1 |            |                       | LOCAL     |
| 'pam'@'ident.secure.com'| PAM    |           0 |            |                       | SYNC      |
+-------------------------+--------+-------------+------------+-----------------------+-----------+
```

--------------------------------

### BSON_ARRAY_SLICE example: slice from start

Source: https://docs.singlestore.com/cloud/reference/sql-reference/bson-functions/bson-array-slice/

Returns 3 elements from the array starting at index 0. The input is cast to BSON and the output to JSON.

```sql
SELECT BSON_ARRAY_SLICE('[0,1,2,3,4,5]':>BSON, 0,3):>JSON AS Result;
```

```text
+---------+
| Result  |
+---------+
| [0,1,2] |
+---------+
```
Tokens:
Raw