[
https://issues.apache.org/jira/browse/DRILL-7342?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16903953#comment-16903953
]
Denys Ordynskiy commented on DRILL-7342:
----------------------------------------
*MySQL* allows to create column name with spaces and select the data by this
names.
test.csvh
|my column 1|my column 2|
|val 1.1|val 2.1|
|val 1.2|val 2.2|
{noformat}
mysql> CREATE DATABASE my_test_db;
{noformat}
{noformat}
mysql> USE my_test_db;
{noformat}
{noformat}
mysql> CREATE TABLE `table name`(`column 1` VARCHAR(100), `column 2`
VARCHAR(100));
{noformat}
{noformat}
mysql> SHOW VARIABLES LIKE "secure_file_priv";
{noformat}
|Variable_name|Value|
|secure_file_priv|/var/lib/mysql-files/|
{noformat}
cp test.csvh /var/lib/mysql-files/
{noformat}
{noformat}
mysql> LOAD DATA INFILE '/var/lib/mysql-files/test.csvh'
INTO TABLE `table name`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
{noformat}
{noformat}
mysql> SELECT * FROM `table name`;
{noformat}
Result contains spaces in the column names and cell values:
|column 1|column 2|
|val 1.1|val 2.1|
|val 1.2|val 2.2|
> Drill replacing spaces with underlines in the column names of text files with
> headers
> -------------------------------------------------------------------------------------
>
> Key: DRILL-7342
> URL: https://issues.apache.org/jira/browse/DRILL-7342
> Project: Apache Drill
> Issue Type: Bug
> Reporter: Denys Ordynskiy
> Priority: Major
>
> Drill doesn't allow to query csvh columns with spaces.
> *Description:*
> Update Drill ctas format option to generate text file with header:
> {noformat}
> set `store.format` = 'csvh';
> {noformat}
> Create table with column names having spaces:
> {noformat}
> create table dfs.tmp.`csv table with spaces` (`Full Name`) as select 'James
> Bond' from (values(1));
> {noformat}
> Drill wrote column name with space:
> {noformat}
> hadoop fs -cat '/tmp/csv table with spaces/0_0_0.csvh'
> {noformat}
> |Full Name|
> |James Bond|
> Try to query this table without column name:
> {noformat}
> select * from dfs.tmp.`csv table with spaces`;
> {noformat}
> |{color:#ff0000}*Full_Name*{color}|
> |James Bond|
> {color:#ff0000}*Drill replaced space with underline.*{color}
> Try to select `Full Name` column with space:
> {noformat}
> select `Full Name` from dfs.tmp.`csv table with spaces`;
> {noformat}
> Drill
> |Full Name|
> | |
> When I changed space to underline, query returned the data:
> {noformat}
> select `Full_Name` from dfs.tmp.`csv table with spaces`;
> {noformat}
> |Full_Name|
> |James Bond|
> Drill can create csvh text files with spaces in the column names. But it's
> impossible to request data using the original column name.
> *Expected result*
> Space should be available character in the column names.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
