@Rocky I suspect the worst performing portion of a query I'm optimizing is bad because it's broadcasting when it should be shuffling when joining on a heavily skewed column. I want to add a histogram for that column, but I get this error:

mysql> ANALYZE TABLE data_lake.<table> UPDATE HISTOGRAM ON <column>;
*************************** 1. row ***************************
   Table: data_lake.<table>
      Op: histogram
Msg_type: error
Msg_text: Getting syntax error from line 1, column 327 to line 1, column 347. Detail message: invalid percent which should in (0, 100).

Is something misconfigured? What should I check?

This error is likely caused by the internal query that StarRocks generates to sample your data lake table. When you run

ANALYZE TABLE ... UPDATE HISTOGRAM

, StarRocks rewrites this into a

SELECT ... FROM ... SAMPLE(percent)

query. The error

invalid percent which should in (0, 100)

indicates that the calculated sampling percentage for this internal query is either 0 or 100, both of which are outside the exclusive range

(0, 100)

required by the

SAMPLE

clause. Here is what you should check and how to resolve it: 1. Verify Table Statistics and Row Count StarRocks calculates the sampling percentage based on the table's total row count. If StarRocks thinks the table is empty or the row count is unknown (common with newly connected Data Lake catalogs), the calculated percentage may result in 0. * Check row count: Run

SELECT count(*) FROM data_lake.<table>

to ensure StarRocks can read the data. * Collect basic stats first: Before collecting histograms, ensure basic statistics (row count, etc.) are present. Run:

sql
    ANALYZE TABLE data_lake.<table>;

Then try the

UPDATE HISTOGRAM

command again. 2. Explicitly Set the Sampling Ratio The default sampling ratio for histograms is

0.1

(10%). If your table is very small or very large, or if there's a configuration mismatch, this might result in an invalid percentage in the rewritten query. Try forcing a specific ratio:

sql
ANALYZE TABLE data_lake.<table> 
UPDATE HISTOGRAM ON <column>
PROPERTIES (
    "histogram_sample_ratio" = "0.5" -- Try 50%
);

3. Check Version and Catalog Support Histogram support for data lake tables was introduced and expanded in recent versions. If you are on an older version, the internal rewrite logic for external catalogs might be buggy or unsupported. * Hive: Supported since v3.2. * Iceberg: Supported since v3.3 (including STRUCT subfields). * Check your version: Run

SELECT VERSION();

. If you are on a version earlier than 3.2, histograms for external tables are not fully supported. 4. Known Bug: Empty Tables or Partitions There is a known issue in some 3.x versions where

ANALYZE

on an external table with 0 rows or no partitions triggers this exact syntax error because the percentage calculation fails. Ensure your table actually contains data and that the

data_lake

catalog has successfully synchronized the partitions. Why is the column count so high? The error message mentions

column 327 to 347

. This is because the error is occurring in the rewritten query (a long

SELECT

statement with many columns and the

SAMPLE

clause) rather than your short

ANALYZE

statement. This confirms that the issue is in how StarRocks is attempting to sample the external data. Recommendation: If the table is heavily skewed and causing broadcast joins, you can also try a manual Skew Join Hint in your query while waiting for statistics to be fixed:

sql
SELECT * 
FROM large_table [skew|large_table.skew_col('skewed_value')]
JOIN other_table ON ...

This forces the optimizer to handle the skew even without a histogram. References • sql-reference/sql-statements/cbo_stats/ANALYZE_TABLE.md • using_starrocks/Cost_based_optimizer.md • knowledge/usage/best-practice.md