[CH] Support convert Char
type of HiveTable to FixedString
in ClickHouse backend
#6057
Labels
enhancement
New feature or request
Description
Now that the char/varchar type of hive table are always convert to string type in clickhouse backend. And the char type in hive table means the field has fixed bytes, which is suitable to convert to
FixedString
type in clickhouse.And in some suitation, the fixed string type can have better performance that the string type in clickhouse. such as the querys contains
order by
.Here we defined 2 tables, has the same data, the first one is defined as
test_tbl1(id UInt64, d String)
, and the second is defined astest_tbl2(id UInt64, d FixedString(20))
, and we do the queryselect * from test_tbl order by d limit 100 settings max_threads=1
to compare the performanceIn the first table, the result show as below
![image](https://private-user-images.githubusercontent.com/37431499/338864089-565b2dc8-aa28-4e0f-abf3-183f2d9e36b0.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjA3MzU2NTYsIm5iZiI6MTcyMDczNTM1NiwicGF0aCI6Ii8zNzQzMTQ5OS8zMzg4NjQwODktNTY1YjJkYzgtYWEyOC00ZTBmLWFiZjMtMTgzZjJkOWUzNmIwLnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA3MTElMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNzExVDIyMDIzNlomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWJlMmNjNTg4NGY1Yjc4YzlmZGJhMzg4OTMyNzgyYmFmMmRkMjg0MDMyYzJhZjQyNWY3Y2RkOWYxMjM0N2U3ZDkmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.Uk3RD3mlltqzAEFL1X5cH0_CVJGD1CE3GXG4EQaIMVs)
In the second table, the result show as below
![image](https://private-user-images.githubusercontent.com/37431499/338864330-1c56785f-a96d-449f-9bdd-c2487f31f806.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjA3MzU2NTYsIm5iZiI6MTcyMDczNTM1NiwicGF0aCI6Ii8zNzQzMTQ5OS8zMzg4NjQzMzAtMWM1Njc4NWYtYTk2ZC00NDlmLTliZGQtYzI0ODdmMzFmODA2LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDA3MTElMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQwNzExVDIyMDIzNlomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWY1MDY1ODExNDczZTRmYmVhZWY3ODgyZmVhZDM5Y2FjMDRjOGY4YjJiZTkyZTA3ZDA5YjQ3NDJlNjQ4OTBmZTEmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0JmFjdG9yX2lkPTAma2V5X2lkPTAmcmVwb19pZD0wIn0.GRB-gohH7unb92EmGq8Q9s9709GGRXYit5h2UM5ZGQs)
and we can see that abort 20% ~ 30% performance improve when the table field is defined as
FixedString
type.The text was updated successfully, but these errors were encountered: