Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-7390

Make single quote character optional and configurable in BeeLine CSV/TSV output

    XMLWordPrintableJSON

Details

    • New Feature
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 0.13.1
    • 0.14.0
    • Clients
    • None
    • Incompatible change
    • Hide
      --outputformat=[table/vertical/csv/tsv/dsv]
      Format mode for result display. Default is table.
      Usage: beeline --outputformat=tsv

      --delimiterForDSV=DELIMITER
      specify the delimiter for delimiter-separated values output format (default: |)
      Usage: beeline --outputformat=dsv --delimiterForDSV=,

      beeline dsv and delimiterForDSV examples are as followings:
      % bin/beeline
      Hive version 0.11.0-SNAPSHOT by Apache
      beeline> !connect jdbc:hive2://localhost:10000 scott tiger org.apache.hive.jdbc.HiveDriver
      !connect jdbc:hive2://localhost:10000 scott tiger org.apache.hive.jdbc.HiveDriver
      Connecting to jdbc:hive2://localhost:10000
      Connected to: Hive (version 0.14.0-SNAPSHOT)
      Driver: Hive (version 0.14.0-SNAPSHOT)
      Transaction isolation: TRANSACTION_REPEATABLE_READ
      HiveServer2 Clients – dsv Example
      0: jdbc:hive2://localhost:10000> create table csv_table(id int, name string, info string) row format delimited fields terminated by '\t';
      No rows affected (0.121 seconds)
      0: jdbc:hive2://localhost:10000> load data local inpath '/root/names' overwrite into table csv_table;
      No rows affected (0.245 seconds)
      0: jdbc:hive2://localhost:10000> select * from csv_table;
      +---------------+-----------------+-----------------+--+
      | csv_table.id | csv_table.name | csv_table.info |
      +---------------+-----------------+-----------------+--+
      | 19630001 | "john" | lennon |
      | 19630002 | peter,paul | mccartney |
      | 19630003 | george | harrison |
      | 19630004 | ringo | starr |
      +---------------+-----------------+-----------------+--+
      4 rows selected (0.09 seconds)
      0: jdbc:hive2://localhost:10000> !outformat csv
      Unknown command: outformat csv
      0: jdbc:hive2://localhost:10000> !outputformat csv
      0: jdbc:hive2://localhost:10000> select * from csv_table;
      csv_table.id,csv_table.name,csv_table.info
      19630001,"""john""",lennon
      19630002,"peter,paul",mccartney
      19630003,george,harrison
      19630004,ringo,starr
      4 rows selected (0.105 seconds)
      0: jdbc:hive2://localhost:10000> !outputformat dsv
      0: jdbc:hive2://localhost:10000> select * from csv_table;
      csv_table.id|csv_table.name|csv_table.info
      19630001|"""john"""|lennon
      19630002|peter,paul|mccartney
      19630003|george|harrison
      19630004|ringo|starr
      4 rows selected (0.123 seconds)
      0: jdbc:hive2://localhost:10000> !set delimiterForDSV ',';
      0: jdbc:hive2://localhost:10000> select * from csv_table;
      csv_table.id'csv_table.name'csv_table.info
      19630001'"""john"""'lennon
      19630002'peter,paul'mccartney
      19630003'george'harrison
      19630004'ringo'starr
      4 rows selected (0.11 seconds)
      Show
      --outputformat=[table/vertical/csv/tsv/dsv] Format mode for result display. Default is table. Usage: beeline --outputformat=tsv --delimiterForDSV=DELIMITER specify the delimiter for delimiter-separated values output format (default: |) Usage: beeline --outputformat=dsv --delimiterForDSV=, beeline dsv and delimiterForDSV examples are as followings: % bin/beeline Hive version 0.11.0-SNAPSHOT by Apache beeline> !connect jdbc: hive2://localhost:10000 scott tiger org.apache.hive.jdbc.HiveDriver !connect jdbc: hive2://localhost:10000 scott tiger org.apache.hive.jdbc.HiveDriver Connecting to jdbc: hive2://localhost:10000 Connected to: Hive (version 0.14.0-SNAPSHOT) Driver: Hive (version 0.14.0-SNAPSHOT) Transaction isolation: TRANSACTION_REPEATABLE_READ HiveServer2 Clients – dsv Example 0: jdbc: hive2://localhost:10000 > create table csv_table(id int, name string, info string) row format delimited fields terminated by '\t'; No rows affected (0.121 seconds) 0: jdbc: hive2://localhost:10000 > load data local inpath '/root/names' overwrite into table csv_table; No rows affected (0.245 seconds) 0: jdbc: hive2://localhost:10000 > select * from csv_table; +---------------+-----------------+-----------------+--+ | csv_table.id | csv_table.name | csv_table.info | +---------------+-----------------+-----------------+--+ | 19630001 | "john" | lennon | | 19630002 | peter,paul | mccartney | | 19630003 | george | harrison | | 19630004 | ringo | starr | +---------------+-----------------+-----------------+--+ 4 rows selected (0.09 seconds) 0: jdbc: hive2://localhost:10000 > !outformat csv Unknown command: outformat csv 0: jdbc: hive2://localhost:10000 > !outputformat csv 0: jdbc: hive2://localhost:10000 > select * from csv_table; csv_table.id,csv_table.name,csv_table.info 19630001,"""john""",lennon 19630002,"peter,paul",mccartney 19630003,george,harrison 19630004,ringo,starr 4 rows selected (0.105 seconds) 0: jdbc: hive2://localhost:10000 > !outputformat dsv 0: jdbc: hive2://localhost:10000 > select * from csv_table; csv_table.id|csv_table.name|csv_table.info 19630001|"""john"""|lennon 19630002|peter,paul|mccartney 19630003|george|harrison 19630004|ringo|starr 4 rows selected (0.123 seconds) 0: jdbc: hive2://localhost:10000 > !set delimiterForDSV ','; 0: jdbc: hive2://localhost:10000 > select * from csv_table; csv_table.id'csv_table.name'csv_table.info 19630001'"""john"""'lennon 19630002'peter,paul'mccartney 19630003'george'harrison 19630004'ringo'starr 4 rows selected (0.11 seconds)

    Description

      Currently when either the CSV or TSV output formats are used in beeline each column is wrapped in single quotes. Quote wrapping of columns should be optional and the user should be able to choose the character used to wrap the columns.

      Attachments

        1. HIVE-7390.1.patch
          7 kB
          ferdinand xu
        2. HIVE-7390.2.patch
          7 kB
          ferdinand xu
        3. HIVE-7390.3.patch
          7 kB
          ferdinand xu
        4. HIVE-7390.4.patch
          8 kB
          Ferdinand Xu
        5. HIVE-7390.5.patch
          9 kB
          Ferdinand Xu
        6. HIVE-7390.6.patch
          9 kB
          Ferdinand Xu
        7. HIVE-7390.7.patch
          9 kB
          Ferdinand Xu
        8. HIVE-7390.8.patch
          9 kB
          Ferdinand Xu
        9. HIVE-7390.9.patch
          9 kB
          Ferdinand Xu
        10. HIVE-7390.patch
          4 kB
          Jim Halfpenny

        Issue Links

          Activity

            People

              Ferd Ferdinand Xu
              jhalfpenny Jim Halfpenny
              Votes:
              0 Vote for this issue
              Watchers:
              12 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: