Table Functions
Table functions are methods for constructing tables or streams from various data sources or formats. They allow you to read data from files, databases, or other sources directly into a Timeplus stream or table.
values
The values
table function allows you to create temporary storage which fills
columns with values. It is useful for quick testing or generating sample data.
The basic syntax of the values
table function is:
VALUES([structure,] values...)
It is commonly used as:
VALUES(
['column1_name Type1, column2_name Type2, ...'],
(value1_row1, value2_row1, ...),
(value1_row2, value2_row2, ...),
...
)
column1_name Type1, ...
(optional). string specifying the column names and types. If this argument is omitted columns will be named asc1
,c2
, etc.(value1_row1, value2_row1)
. tuples containing values of any type.
Comma separated tuples can be replaced by single values as well. In this case each value is taken to be a new row.
Returns a temporary table containing the provided values.
SELECT *
FROM VALUES(
'person string, place string',
('Noah', 'Paris'),
('Emma', 'Tokyo'),
('Liam', 'Sydney'),
('Olivia', 'Berlin'),
('Ilya', 'London'),
('Sophia', 'London'),
('Jackson', 'Madrid'),
('Alexey', 'Amsterdam'),
('Mason', 'Venice'),
('Isabella', 'Prague')
)
┌─person───┬─place─────┐
1. │ Noah │ Paris │
2. │ Emma │ Tokyo │
3. │ Liam │ Sydney │
4. │ Olivia │ Berlin │
5. │ Ilya │ London │
6. │ Sophia │ London │
7. │ Jackson │ Madrid │
8. │ Alexey │ Amsterdam │
9. │ Mason │ Venice │
10. │ Isabella │ Prague │
└──────────┴───────────┘
VALUES
can also be used with single values rather than tuples. For example:
SELECT *
FROM VALUES(
'person string',
'Noah',
'Emma',
'Liam',
'Olivia',
'Ilya',
'Sophia',
'Jackson',
'Alexey',
'Mason',
'Isabella'
)
┌─person───┐
1. │ Noah │
2. │ Emma │
3. │ Liam │
4. │ Olivia │
5. │ Ilya │
6. │ Sophia │
7. │ Jackson │
8. │ Alexey │
9. │ Mason │
10. │ Isabella │
└──────────┘
Or without providing a row specification ('column1_name type1, column2_name type2, ...'
in the syntax), in which case the columns are automatically named.
For example:
-- tuples as values
SELECT *
FROM VALUES(
('Noah', 'Paris'),
('Emma', 'Tokyo'),
('Liam', 'Sydney'),
('Olivia', 'Berlin'),
('Ilya', 'London'),
('Sophia', 'London'),
('Jackson', 'Madrid'),
('Alexey', 'Amsterdam'),
('Mason', 'Venice'),
('Isabella', 'Prague')
)
┌─c1───────┬─c2────────┐
1. │ Noah │ Paris │
2. │ Emma │ Tokyo │
3. │ Liam │ Sydney │
4. │ Olivia │ Berlin │
5. │ Ilya │ London │
6. │ Sophia │ London │
7. │ Jackson │ Madrid │
8. │ Alexey │ Amsterdam │
9. │ Mason │ Venice │
10. │ Isabella │ Prague │
└──────────┴───────────┘
-- single values
SELECT *
FROM VALUES(
'Noah',
'Emma',
'Liam',
'Olivia',
'Ilya',
'Sophia',
'Jackson',
'Alexey',
'Mason',
'Isabella'
)
┌─c1───────┐
1. │ Noah │
2. │ Emma │
3. │ Liam │
4. │ Olivia │
5. │ Ilya │
6. │ Sophia │
7. │ Jackson │
8. │ Alexey │
9. │ Mason │
10. │ Isabella │
└──────────┘
zeros
zeros(N)
– Returns a table with the single zero
column (uint8) that contains N zeros.
zeros_mt
zeros_mt(N)
– Returns a table with the single zero
column (uint8) that contains N zeros, using multiple threads for faster generation.