- Introduction
- Quick Start
- Table Design
- Data Loading
- Data Export
- Using StarRocks
- Reference
- SQL Reference
- User Account Management
- Cluster Management
- ADMIN CANCEL REPAIR
- ADMIN CHECK TABLET
- ADMIN REPAIR
- ADMIN SET CONFIG
- ADMIN SET REPLICA STATUS
- ADMIN SHOW CONFIG
- ADMIN SHOW REPLICA DISTRIBUTION
- ADMIN SHOW REPLICA STATUS
- ALTER SYSTEM
- CANCEL DECOMMISSION
- CREATE FILE
- DROP FILE
- INSTALL PLUGIN
- SHOW BACKENDS
- SHOW BROKER
- SHOW FILE
- SHOW FRONTENDS
- SHOW FULL COLUMNS
- SHOW INDEX
- SHOW PLUGINS
- SHOW TABLE STATUS
- UNINSTALL PLUGIN
- DDL
- ALTER DATABASE
- ALTER TABLE
- ALTER VIEW
- BACKUP
- CANCEL BACKUP
- CANCEL RESTORE
- CREATE DATABASE
- CREATE INDEX
- CREATE MATERIALIZED VIEW
- CREATE REPOSITORY
- CREATE RESOURCE
- CREATE TABLE AS SELECT
- CREATE TABLE LIKE
- CREATE TABLE
- CREATE VIEW
- CREATE FUNCTION
- DROP DATABASE
- DROP INDEX
- DROP MATERIALIZED VIEW
- DROP REPOSITORY
- DROP RESOURCE
- DROP TABLE
- DROP VIEW
- DROP FUNCTION
- HLL
- RECOVER
- RESTORE
- SHOW RESOURCES
- SHOW FUNCTION
- TRUNCATE TABLE
- DML
- ALTER ROUTINE LOAD
- BROKER LOAD
- CANCEL LOAD
- DELETE
- EXPORT
- GROUP BY
- INSERT
- PAUSE ROUTINE LOAD
- RESUME ROUTINE LOAD
- ROUTINE LOAD
- SELECT
- SHOW ALTER
- SHOW BACKUP
- SHOW DATA
- SHOW DATABASES
- SHOW DELETE
- SHOW DYNAMIC PARTITION TABLES
- SHOW EXPORT
- SHOW LOAD
- SHOW PARTITIONS
- SHOW PROPERTY
- SHOW REPOSITORIES
- SHOW RESTORE
- SHOW ROUTINE LOAD
- SHOW ROUTINE LOAD TASK
- SHOW SNAPSHOT
- SHOW TABLES
- SHOW TABLET
- SHOW TRANSACTION
- SPARK LOAD
- STOP ROUTINE LOAD
- STREAM LOAD
- Data Type
- Auxiliary Commands
- Function Reference
- Date Functions
- Geographic Functions
- String Functions
- Aggregation Functions
- Bitmap Functions
- Array Functions
- cast function
- hash function
- Crytographic Functions
- Math Functions
- Utility Functions
- System variables
- Error code
- System limits
- SQL Reference
- Administration
- FAQ
- Deployment
- Data Migration
- SQL
- Others FAQs
- Benchmark
- Developers
- Contribute to StarRocks
- Code Style Guides
- Use the debuginfo file for debugging
- Development Environment
- Trace Tools
- Integration
SQL
This topic provides answers to some frequently asked questions about SQL.
This error "fail to allocate memory." when I build a materialized view
To solve this problem, increase the value of the memory_limitation_per_thread_for_schema_change
parameter in the be.conf file. This parameter refers to the maximum storage that can be allocated for a single task to change the scheme. The default value of the maximum storage is 2 GB.
Does StarRocks have limitations on query result caching?
StarRocks does not support result caching. StarRocks enables the page cache to cache the query result for repetitive use. This allows subsequent query execution to get results directly from the cache. You can set the storage_page_cache_limit
parameter in the be.conf file to specify the size of the page cache. The default size of the page cache is 20 GB.
When a Null
is included in the calculation, the calculation results of functions are false except for the ISNULL() function
In standard SQL, every calculation that includes an operand with a NULL
value returns a NULL
.
Why is the query result incorrect after I enclose quotation marks around a value of the BIGINT data type for an equivalence query?
Problem description
See the following examples:
select cust_id,idno
from llyt_dev.dwd_mbr_custinfo_dd
where Pt= ‘2021-06-30’
and cust_id = ‘20210129005809043707’
limit 10 offset 0;
+---------------------+-----------------------------------------+
| cust_id | idno |
+---------------------+-----------------------------------------+
| 20210129005809436 | yjdgjwsnfmdhjw294F93kmHCNMX39dw= |
| 20210129005809436 | sdhnswjwijeifme3kmHCNMX39gfgrdw= |
| 20210129005809436 | Tjoedk3js82nswndrf43X39hbggggbw= |
| 20210129005809436 | denuwjaxh73e39592jwshbnjdi22ogw= |
| 20210129005809436 | ckxwmsd2mei3nrunjrihj93dm3ijin2= |
| 20210129005809436 | djm2emdi3mfi3mfu4jro2ji2ndimi3n= |
+---------------------+-----------------------------------------+
select cust_id,idno
from llyt_dev.dwd_mbr_custinfo_dd
where Pt= ‘2021-06-30’
and cust_id = 20210129005809043707
limit 10 offset 0;
+---------------------+-----------------------------------------+
| cust_id | idno |
+---------------------+-----------------------------------------+
| 20210189979989976 | xuywehuhfuhruehfurhghcfCNMX39dw= |
+---------------------+-----------------------------------------+
Solution
When you compare the STRING data type and the INTEGER data type, the fields of these two types are cast to the DOUBLE data type. Therefore, quotation marks cannot be added. Otherwise, the condition defined in the WHERE clause cannot be indexed.
Does StarRocks support the DECODE function?
StarRocks does not support the DECODE function of the Oracle database. StarRocks is compatible with MySQL, so you can use the CASE WHEN statement.
Can the latest data be queried immediately after data is loaded into the primary key model of StarRocks?
Yes. StarRocks merges data in a way that references Google Mesa. In StarRocks, a BE triggers the data merge and it has two kinds of compaction to merge data. If the data merge is not completed, it is finished during your query. Therefore, you can read the latest data after data loading.
Do the utf8mb4 characters stored in StarRocks get truncated or appear garbled?
No.
This error "table's state is not normal" occurs when I run the alter table
command
This error occurs because the previous alteration has not been completed. You can run the following code to check the status of the previous alteration:
show tablet from lineitem where State="ALTER";
The time spent on the alteration operation relates to the data volume. In general, the alteration can be completed in minutes. We recommend that you stop loading data into StarRocks while you are altering tables because data loading lowers the speed at which alteration completes.
This error "get partition detail failed: org.apache.doris.common.DdlException: get hive partition meta data failed: java.net.UnknownHostException:hadooptest" occurs when I query the external tables of Apache Hive
This error occurs when the metadata of Apache Hive partitions cannot be obtained. To solve this problem, copy core-sit.xml and hdfs-site.xml to the fe.conf file and the be.conf file.
This error "planner use long time 3000 remaining task num 1" occurs when I query data
This error occurs usually due to a full garbage collection (full GC), which can be checked by using backend monitoring and the fe.gc log. To solve this problem, perform one of the following operations:
- Allows SQL's client to access multiple frontends (FEs) simultaneously to spread the load.
- Change the heap size of Java Virtual Machine (JVM) from 8 GB to 16 GB in the fe.conf file to increase memory and reduce the impact of full GC.
When cardinality of column A is small, the query results of select B from tbl order by A limit 10
vary each time
SQL can only guarantee that column A is ordered, and it cannot guarantee that the order of column B is the same for each query. MySQL can guarantee the order of column A and column B because it is a standalone database.
StarRocks is a distributed database, of which data stored in the underlying table is in a sharding pattern. The data of column A is distributed across multiple machines, so the order of column B returned by multiple machines may be different for each query, resulting in inconsistent order of B each time. To solve this problem, change select B from tbl order by A limit 10
to select B from tbl order by A,B limit 10
.
Why is there a large gap in column efficiency between SELECT * and SELECT?
To solve this problem, check the profile and see MERGE details:
Check whether the aggregation on the storage layer takes up too much time.
Check whether there are too many indicator columns. If so, aggregate hundreds of columns of millions of rows.
MERGE:
- aggr: 26s270ms
- sort: 15s551ms
Does DELETE support nested functions?
Nested functions are not supported, such as to_days(now())
in DELETE from test_new WHERE to_days(now())-to_days(publish_time) >7;
.
How to improve the usage efficiency of a database when there are hundreds of tables in it?
To improve efficiency, add the -A
parameter when you connect to MySQL's client server: mysql -uroot -h127.0.0.1 -P8867 -A
. MySQL's client server does not pre-read database information.
How to reduce the disk space occupied by the BE log and the FE log?
Adjust the log level and corresponding parameters. For more information, see Parameter Configuration.
This error "table *** is colocate table, cannot change replicationNum" occurs when I modify the replication number
When you create colocated tables, you need to set the group
property. Therefore, you cannot modify the replication number for a single table. You can perform the following steps to modify the replication number for all tables in a group:
- Set
group_with
toempty
for all tables in a group. - Set a proper
replication_num
for all tables in a group. - Set
group_with
back to its original value.
Does setting VARCHAR to the maximum value affect storage?
VARCHAR is a variable-length data type, which has a specified length that can be changed based on the actual data length. Specifying a different varchar length when you create a table has little impact on the query performance on the same data.
This error "create partition timeout" occurs when I truncate a table
To truncate a table, you need to create the corresponding partitions and then swap them. If there are a larger number of partitions that need to be created, this error occurs. In addition, if there are many data load tasks, the lock will be held for a long time during the compaction process. Therefore, the lock cannot be acquired when you create tables. If there are too many data load tasks, set tablet_map_shard_size
to 512
in the be.conf file to reduce the lock contention.
This error "Failed to specify server's Kerberos principal name" occurs when I access external tables of Apache Hive
Add the following information to hdfs-site.xml in the fe.conf file and the be.conf file:
<property>
<name>dfs.namenode.kerberos.principal.pattern</name>
<value>*</value>
</property>
Is "2021-10" a date format in StarRocks?
No.
Can "2021-10" be used as a partition field?
No, use functions to change "2021-10" to "2021-10-01" and then use "2021-10-01" as a partition field.
- SQL
- This error "fail to allocate memory." when I build a materialized view
- Does StarRocks have limitations on query result caching?
- When a Null is included in the calculation, the calculation results of functions are false except for the ISNULL() function
- Why is the query result incorrect after I enclose quotation marks around a value of the BIGINT data type for an equivalence query?
- Does StarRocks support the DECODE function?
- Can the latest data be queried immediately after data is loaded into the primary key model of StarRocks?
- Do the utf8mb4 characters stored in StarRocks get truncated or appear garbled?
- This error "table's state is not normal" occurs when I run the alter table command
- This error "get partition detail failed: org.apache.doris.common.DdlException: get hive partition meta data failed: java.net.UnknownHostException:hadooptest" occurs when I query the external tables of Apache Hive
- This error "planner use long time 3000 remaining task num 1" occurs when I query data
- When cardinality of column A is small, the query results of select B from tbl order by A limit 10 vary each time
- Why is there a large gap in column efficiency between SELECT * and SELECT?
- Does DELETE support nested functions?
- How to improve the usage efficiency of a database when there are hundreds of tables in it?
- How to reduce the disk space occupied by the BE log and the FE log?
- This error "table *** is colocate table, cannot change replicationNum" occurs when I modify the replication number
- Does setting VARCHAR to the maximum value affect storage?
- This error "create partition timeout" occurs when I truncate a table
- This error "Failed to specify server's Kerberos principal name" occurs when I access external tables of Apache Hive
- Is "2021-10" a date format in StarRocks?
- Can "2021-10" be used as a partition field?