http://starrocks.io logo
Join Slack
Powered by
l
@Rocky hello
r
Hello! I'm Rocky, your StarRocks assistant. How can I help you today?
l
@Rocky Exception: Failed to insert into SALESDATA: { "TxnId": 26700469, "Label": "4ff4c990-5708-4b29-a3d8-984631b7779b", "Db": "qtdm", "Table": "salesdata", "Status": "Fail", "Message": "Getting syntax error at line 1, column 21. Detail message: No viable statement for input 'HR CODE'.", "NumberTotalRows": 0, "NumberLoadedRows": 0, "NumberFilteredRows": 0, "NumberUnselectedRows": 0, "LoadBytes": 0, "LoadTimeMs": 0, "BeginTxnTimeMs": 0, "StreamLoadPlanTimeMs": 1, "ReadDataTimeMs": 0, "WriteDataTimeMs": 0, "CommitAndPublishTimeMs": 0 } -- qtdm.salesdata definition CREATE TABLE 
salesdata
( 
Class
varchar(50) NULL COMMENT "", 
No
decimal(38, 20) NULL COMMENT "", 
HR Code
varchar(50) NULL COMMENT "", 
Channel
varchar(50) NULL COMMENT "", 
Sale Code
varchar(50) NULL COMMENT "", 
Sale Name
varchar(65533) NULL COMMENT "", 
Contract Type
varchar(50) NULL COMMENT "", 
OB Date
varchar(50) NULL COMMENT "", 
Status
varchar(50) NULL COMMENT "", 
Team Name
varchar(50) NULL COMMENT "", 
Team Leader Name
varchar(65533) NULL COMMENT "", 
Team Leader Code
varchar(50) NULL COMMENT "", 
Supervisor Name
varchar(50) NULL COMMENT "", 
Supervisor Code
varchar(50) NULL COMMENT "", 
UH Name
varchar(50) NULL COMMENT "", 
UH Code
varchar(50) NULL COMMENT "", 
RSM Name
varchar(50) NULL COMMENT "", 
RSM Code
varchar(50) NULL COMMENT "", 
Head Name
varchar(50) NULL COMMENT "", 
Head Code
varchar(50) NULL COMMENT "", 
Region
varchar(50) NULL COMMENT "", 
Sub Region
varchar(50) NULL COMMENT "", 
Province
varchar(50) NULL COMMENT "", 
Sub Province
varchar(50) NULL COMMENT "", 
Quit Date
varchar(50) NULL COMMENT "", 
Modify Date
varchar(50) NULL COMMENT "", 
Reason
varchar(255) NULL COMMENT "", 
DOB year
decimal(38, 20) NULL COMMENT "", 
Giới tính
varchar(50) NULL COMMENT "", 
Tỉnh thành sinh sống
varchar(50) NULL COMMENT "", 
Contract Type (Update)
varchar(50) NULL COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(
Class
, 
No
) DISTRIBUTED BY HASH(
Class
) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" );
r
The error 
No viable statement for input 'HR CODE'
occurs because the column name 
HR Code
 contains a space. In StarRocks, any identifier (like a column or table name) that contains spaces, special characters, or reserved keywords must be enclosed in backticks (`` ` ``). To fix this during a Stream Load, you must wrap all column names that have spaces or special characters (including 
Giới tính
, 
Contract Type (Update)
, etc.) in backticks within the 
columns
header. Solution If you are using 
curl
for Stream Load, your command should look like this (note the backticks for the specific columns):
Copy code
bash
curl --location-trusted -u <username>:<password> \
    -H "label:4ff4c990-5708-4b29-a3d8-984631b7779b" \
    -H "columns: Class, No, \`HR Code\`, Channel, \`Sale Code\`, \`Sale Name\`, \`Contract Type\`, \`OB Date\`, Status, \`Team Name\`, \`Team Leader Name\`, \`Team Leader Code\`, \`Supervisor Name\`, \`Supervisor Code\`, \`UH Name\`, \`UH Code\`, \`RSM Name\`, \`RSM Code\`, \`Head Name\`, \`Head Code\`, Region, \`Sub Region\`, Province, \`Sub Province\`, \`Quit Date\`, \`Modify Date\`, Reason, \`DOB year\`, \`Giới tính\`, \`Tỉnh thành sinh sống\`, \`Contract Type (Update)\`" \
    -T data.csv \
    http://<FE_IP>:<FE_HTTP_PORT>/api/qtdm/salesdata/_stream_load
Key Points: 1. Backticks for Spaces: Any column name with a space (e.g., 
HR Code
) or parentheses (e.g., 
Contract Type (Update)
) must be enclosed in backticks: `` 
HR Code
``. 2. Shell Escaping: When using backticks inside a double-quoted string in a shell (like 
bash
), you must escape them with a backslash: `` \` ``. Alternatively, use a dollar-sign single-quote string: `$'columns: \`HR Code\`, ...'`. 3. Non-ASCII Characters: Columns like 
Giới tính
also require backticks to ensure the parser handles the characters and spaces correctly. 4. Case Sensitivity: Ensure the column names in your 
columns
header match the case in the 
CREATE TABLE
definition exactly, as StarRocks identifiers can be case-sensitive depending on the configuration. If you are using a specific library (Python, Java, etc.) to perform the load, ensure the string sent in the 
columns
HTTP header includes those backticks. References • faq/loading/Stream_load_faq.md • sql-reference/sql-statements/loading_unloading/STREAM_LOAD.md • loading/Etl_in_loading.md
l
@Rocky CREATE TABLE 
salesdata
( 
Class
varchar(50) NULL COMMENT "", 
No
decimal(38, 20) NULL COMMENT "", 
HR Code
varchar(50) NULL COMMENT "", 
Channel
varchar(50) NULL COMMENT "", 
Sale Code
varchar(50) NULL COMMENT "", 
Sale Name
varchar(65533) NULL COMMENT "", 
Contract Type
varchar(50) NULL COMMENT "", 
OB Date
varchar(50) NULL COMMENT "", 
Status
varchar(50) NULL COMMENT "", 
Team Name
varchar(50) NULL COMMENT "", 
Team Leader Name
varchar(65533) NULL COMMENT "", 
Team Leader Code
varchar(50) NULL COMMENT "", 
Supervisor Name
varchar(50) NULL COMMENT "", 
Supervisor Code
varchar(50) NULL COMMENT "", 
UH Name
varchar(50) NULL COMMENT "", 
UH Code
varchar(50) NULL COMMENT "", 
RSM Name
varchar(50) NULL COMMENT "", 
RSM Code
varchar(50) NULL COMMENT "", 
Head Name
varchar(50) NULL COMMENT "", 
Head Code
varchar(50) NULL COMMENT "", 
Region
varchar(50) NULL COMMENT "", 
Sub Region
varchar(50) NULL COMMENT "", 
Province
varchar(50) NULL COMMENT "", 
Sub Province
varchar(50) NULL COMMENT "", 
Quit Date
varchar(50) NULL COMMENT "", 
Modify Date
varchar(50) NULL COMMENT "", 
Reason
varchar(255) NULL COMMENT "", 
DOB year
decimal(38, 20) NULL COMMENT "", 
Giới tính
varchar(50) NULL COMMENT "", 
Tỉnh thành sinh sống
varchar(50) NULL COMMENT "", 
Contract Type (Update)
varchar(50) NULL COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(
Class
, 
No
) DISTRIBUTED BY HASH(
Class
) BUCKETS 8 PROPERTIES ( "compression" = "LZ4", "fast_schema_evolution" = "true", "replicated_storage" = "true", "replication_num" = "2" ); change to colume without space, used _ instead
r
Here is the updated 
CREATE TABLE
statement with all spaces in column names replaced by underscores (
_
). I have also removed the parentheses from 
Contract Type (Update)
to ensure the column names are clean and easy to query.
Copy code
sql
CREATE TABLE `salesdata` (
  `Class` varchar(50) NULL COMMENT "",
  `No` decimal(38, 20) NULL COMMENT "",
  `HR_Code` varchar(50) NULL COMMENT "",
  `Channel` varchar(50) NULL COMMENT "",
  `Sale_Code` varchar(50) NULL COMMENT "",
  `Sale_Name` varchar(65533) NULL COMMENT "",
  `Contract_Type` varchar(50) NULL COMMENT "",
  `OB_Date` varchar(50) NULL COMMENT "",
  `Status` varchar(50) NULL COMMENT "",
  `Team_Name` varchar(50) NULL COMMENT "",
  `Team_Leader_Name` varchar(65533) NULL COMMENT "",
  `Team_Leader_Code` varchar(50) NULL COMMENT "",
  `Supervisor_Name` varchar(50) NULL COMMENT "",
  `Supervisor_Code` varchar(50) NULL COMMENT "",
  `UH_Name` varchar(50) NULL COMMENT "",
  `UH_Code` varchar(50) NULL COMMENT "",
  `RSM_Name` varchar(50) NULL COMMENT "",
  `RSM_Code` varchar(50) NULL COMMENT "",
  `Head_Name` varchar(50) NULL COMMENT "",
  `Head_Code` varchar(50) NULL COMMENT "",
  `Region` varchar(50) NULL COMMENT "",
  `Sub_Region` varchar(50) NULL COMMENT "",
  `Province` varchar(50) NULL COMMENT "",
  `Sub_Province` varchar(50) NULL COMMENT "",
  `Quit_Date` varchar(50) NULL COMMENT "",
  `Modify_Date` varchar(50) NULL COMMENT "",
  `Reason` varchar(255) NULL COMMENT "",
  `DOB_year` decimal(38, 20) NULL COMMENT "",
  `Giới_tính` varchar(50) NULL COMMENT "",
  `Tỉnh_thành_sinh_sống` varchar(50) NULL COMMENT "",
  `Contract_Type_Update` varchar(50) NULL COMMENT ""
) ENGINE=OLAP
DUPLICATE KEY(`Class`, `No`)
DISTRIBUTED BY HASH(`Class`) BUCKETS 8
PROPERTIES (
"compression" = "LZ4",
"fast_schema_evolution" = "true",
"replicated_storage" = "true",
"replication_num" = "2"
);
Important Note for Loading: Since you changed the column names in the table, you must also update your Stream Load 
columns
header to match these new names. If your source file still has headers with spaces, you can map them like this:
Copy code
bash
-H "columns: Class, No, HR_Code, ..., Contract_Type_Update"
If the CSV file does not have headers and you are mapping them manually, ensure the order in the 
columns
header matches the order of columns in your CSV file.
l
@Rocky viết câu select tên cột cũ (có cách) as tên cột mới (có _)
r
Đây là câu lệnh 
SELECT
để chuyển đổi các cột có khoảng trắng sang tên cột mới có dấu gạch dưới (
_
). Bạn cần đặt tên cột cũ trong dấu ngoặc kép (hoặc backticks `` ` ``) để StarRocks nhận diện được khoảng trắng:
Copy code
sql
SELECT 
    `Class`,
    `No`,
    `HR Code` AS `HR_Code`,
    `Channel`,
    `Sale Code` AS `Sale_Code`,
    `Sale Name` AS `Sale_Name`,
    `Contract Type` AS `Contract_Type`,
    `OB Date` AS `OB_Date`,
    `Status`,
    `Team Name` AS `Team_Name`,
    `Team Leader Name` AS `Team_Leader_Name`,
    `Team Leader Code` AS `Team_Leader_Code`,
    `Supervisor Name` AS `Supervisor_Name`,
    `Supervisor Code` AS `Supervisor_Code`,
    `UH Name` AS `UH_Name`,
    `UH Code` AS `UH_Code`,
    `RSM Name` AS `RSM_Name`,
    `RSM Code` AS `RSM_Code`,
    `Head Name` AS `Head_Name`,
    `Head Code` AS `Head_Code`,
    `Region`,
    `Sub Region` AS `Sub_Region`,
    `Province`,
    `Sub Province` AS `Sub_Province`,
    `Quit Date` AS `Quit_Date`,
    `Modify Date` AS `Modify_Date`,
    `Reason`,
    `DOB year` AS `DOB_year`,
    `Giới tính` AS `Giới_tính`,
    `Tỉnh thành sinh sống` AS `Tỉnh_thành_sinh_sống`,
    `Contract Type (Update)` AS `Contract_Type_Update`
FROM `qtdm`.`salesdata`;
Lưu ý: 1. Dấu Backticks (`` ` ``): Bắt buộc phải dùng cho các cột có khoảng trắng như `` 
HR Code
`` hoặc có ký tự đặc biệt như `` 
Contract Type (Update)
``. 2. Tiếng Việt: Các cột như `` 
Giới tính
`` cũng cần bọc trong backticks để tránh lỗi font hoặc lỗi hoặc lỗi cú pháp khi có khoảng trắng. 3. Alias (AS): Bạn có thể dùng 
AS
để đặt lại tên cho sạch sẽ, giúp các câu lệnh truy vấn sau này không cần dùng dấu backticks nữa.
2 Views
Open in Slack
PreviousNext
Powered by