Uploaded image for project: 'CarbonData'
  1. CarbonData
  2. CARBONDATA-650

Columns switching error in performing the string functions

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 1.1.0
    • 1.0.0-incubating
    • data-load
    • Spark 2.1 Cluster mode

    Description

      We tried to execute the string function ASCII(COLUMN_NAME) on the Carbon Table in insert query with the following scenario,

      • Create carbon tables as :
        CREATE TABLE target_uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES ("TABLE_BLOCKSIZE"= "256 MB");

      CREATE TABLE source_uniqdata (CUST_ID int,CUST_NAME String,ACTIVE_EMUI_VERSION string, DOB timestamp, DOJ timestamp, BIGINT_COLUMN1 bigint,BIGINT_COLUMN2 bigint,DECIMAL_COLUMN1 decimal(30,10), DECIMAL_COLUMN2 decimal(36,10),Double_COLUMN1 double, Double_COLUMN2 double,INTEGER_COLUMN1 int) STORED BY 'org.apache.carbondata.format' TBLPROPERTIES ("TABLE_BLOCKSIZE"= "256 MB");

      • Load records into source carbon table as :
        LOAD DATA inpath 'hdfs://hadoop-master:54311/data/uniqdata/2000_UniqData.csv' INTO table source_uniqdata options('DELIMITER'=',', 'FILEHEADER'='CUST_ID, CUST_NAME, ACTIVE_EMUI_VERSION, DOB, DOJ, BIGINT_COLUMN1, BIGINT_COLUMN2, DECIMAL_COLUMN1, DECIMAL_COLUMN2, Double_COLUMN1, Double_COLUMN2, INTEGER_COLUMN1');
      • Insert the records of source table into target table and apply the string function

      insert into table target_uniqdata select CUST_ID, ASCII(CUST_NAME), ACTIVE_EMUI_VERSION, DOB, DOJ, BIGINT_COLUMN1, BIGINT_COLUMN2, DECIMAL_COLUMN1, DECIMAL_COLUMN2, Double_COLUMN1, Double_COLUMN2, INTEGER_COLUMN1 from source_uniqdata;

      Below is the order of columns in the source_uniqdata table,

      ---------------------------------------------------------------------------------------------------------+

      CUST_NAME ACTIVE_EMUI_VERSION DOB DOJ CUST_ID BIGINT_COLUMN1 BIGINT_COLUMN2 DECIMAL_COLUMN1 DECIMAL_COLUMN2 Double_COLUMN1 Double_COLUMN2 INTEGER_COLUMN1

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      CUST_NAME_00987 ACTIVE_EMUI_VERSION_00987 1972-09-14 01:00:03.0 1972-09-14 02:00:03.0 9987 123372037841 -223372035867 12345679888.1234000000 22345679888.1234000000 1.12345674897976E10 -1.12345674897976E10 988
      CUST_NAME_00988 ACTIVE_EMUI_VERSION_00988 1972-09-15 01:00:03.0 1972-09-15 02:00:03.0 9988 123372037842 -223372035866 12345679889.1234000000 22345679889.1234000000 1.12345674897976E10 -1.12345674897976E10 989
      CUST_NAME_00989 ACTIVE_EMUI_VERSION_00989 1972-09-16 01:00:03.0 1972-09-16 02:00:03.0 9989 123372037843 -223372035865 12345679890.1234000000 22345679890.1234000000 1.12345674897976E10 -1.12345674897976E10 990
      CUST_NAME_00990 ACTIVE_EMUI_VERSION_00990 1972-09-17 01:00:03.0 1972-09-17 02:00:03.0 9990 123372037844 -223372035864 12345679891.1234000000 22345679891.1234000000 1.12345674897976E10 -1.12345674897976E10 991
      CUST_NAME_00991 ACTIVE_EMUI_VERSION_00991 1972-09-18 01:00:03.0 1972-09-18 02:00:03.0 9991 123372037845 -223372035863 12345679892.1234000000 22345679892.1234000000 1.12345674897976E10 -1.12345674897976E10 992
      CUST_NAME_00992 ACTIVE_EMUI_VERSION_00992 1972-09-19 01:00:03.0 1972-09-19 02:00:03.0 9992 123372037846 -223372035862 12345679893.1234000000 22345679893.1234000000 1.12345674897976E10 -1.12345674897976E10 993
      CUST_NAME_00993 ACTIVE_EMUI_VERSION_00993 1972-09-20 01:00:03.0 1972-09-20 02:00:03.0 9993 123372037847 -223372035861 12345679894.1234000000 22345679894.1234000000 1.12345674897976E10 -1.12345674897976E10 994
      CUST_NAME_00994 ACTIVE_EMUI_VERSION_00994 1972-09-21 01:00:03.0 1972-09-21 02:00:03.0 9994 123372037848 -223372035860 12345679895.1234000000 22345679895.1234000000 1.12345674897976E10 -1.12345674897976E10 995
      CUST_NAME_00995 ACTIVE_EMUI_VERSION_00995 1972-09-22 01:00:03.0 1972-09-22 02:00:03.0 9995 123372037849 -223372035859 12345679896.1234000000 22345679896.1234000000 1.12345674897976E10 -1.12345674897976E10 996
      CUST_NAME_00996 ACTIVE_EMUI_VERSION_00996 1972-09-23 01:00:03.0 1972-09-23 02:00:03.0 9996 123372037850 -223372035858 12345679897.1234000000 22345679897.1234000000 1.12345674897976E10 -1.12345674897976E10 997
      CUST_NAME_00997 ACTIVE_EMUI_VERSION_00997 1972-09-24 01:00:03.0 1972-09-24 02:00:03.0 9997 123372037851 -223372035857 12345679898.1234000000 22345679898.1234000000 1.12345674897976E10 -1.12345674897976E10 998
      CUST_NAME_00998 ACTIVE_EMUI_VERSION_00998 1972-09-25 01:00:03.0 1972-09-25 02:00:03.0 9998 123372037852 -223372035856 12345679899.1234000000 22345679899.1234000000 1.12345674897976E10 -1.12345674897976E10 999
      CUST_NAME_00999 ACTIVE_EMUI_VERSION_00999 1972-09-26 01:00:03.0 1972-09-26 02:00:03.0 9999 123372037853 -223372035855 12345679900.1234000000 22345679900.1234000000 1.12345674897976E10 -1.12345674897976E10 1000

      ::::: AFTER ASCII OPERATION ::::SPARK 2.1::::::

      ---------------------------------------------------------------------------------

      CUST_NAME ACTIVE_EMUI_VERSION DOB DOJ CUST_ID BIGINT_COLUMN1 BIGINT_COLUMN2 DECIMAL_COLUMN1 DECIMAL_COLUMN2 Double_COLUMN1 Double_COLUMN2 INTEGER_COLUMN1

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      9987 67 NULL 1972-09-14 01:00:03.0 85264203 123372037841 -223372035867 12345679888.1234000000 22345679888.1234000000 1.12345674897976E10 -1.12345674897976E10 988
      9988 67 NULL 1972-09-15 01:00:03.0 85350603 123372037842 -223372035866 12345679889.1234000000 22345679889.1234000000 1.12345674897976E10 -1.12345674897976E10 989
      9989 67 NULL 1972-09-16 01:00:03.0 85437003 123372037843 -223372035865 12345679890.1234000000 22345679890.1234000000 1.12345674897976E10 -1.12345674897976E10 990
      9990 67 NULL 1972-09-17 01:00:03.0 85523403 123372037844 -223372035864 12345679891.1234000000 22345679891.1234000000 1.12345674897976E10 -1.12345674897976E10 991
      9991 67 NULL 1972-09-18 01:00:03.0 85609803 123372037845 -223372035863 12345679892.1234000000 22345679892.1234000000 1.12345674897976E10 -1.12345674897976E10 992
      9992 67 NULL 1972-09-19 01:00:03.0 85696203 123372037846 -223372035862 12345679893.1234000000 22345679893.1234000000 1.12345674897976E10 -1.12345674897976E10 993
      9993 67 NULL 1972-09-20 01:00:03.0 85782603 123372037847 -223372035861 12345679894.1234000000 22345679894.1234000000 1.12345674897976E10 -1.12345674897976E10 994
      9994 67 NULL 1972-09-21 01:00:03.0 85869003 123372037848 -223372035860 12345679895.1234000000 22345679895.1234000000 1.12345674897976E10 -1.12345674897976E10 995
      9995 67 NULL 1972-09-22 01:00:03.0 85955403 123372037849 -223372035859 12345679896.1234000000 22345679896.1234000000 1.12345674897976E10 -1.12345674897976E10 996
      9996 67 NULL 1972-09-23 01:00:03.0 86041803 123372037850 -223372035858 12345679897.1234000000 22345679897.1234000000 1.12345674897976E10 -1.12345674897976E10 997
      9997 67 NULL 1972-09-24 01:00:03.0 86128203 123372037851 -223372035857 12345679898.1234000000 22345679898.1234000000 1.12345674897976E10 -1.12345674897976E10 998
      9998 67 NULL 1972-09-25 01:00:03.0 86214603 123372037852 -223372035856 12345679899.1234000000 22345679899.1234000000 1.12345674897976E10 -1.12345674897976E10 999
      9999 67 NULL 1972-09-26 01:00:03.0 86301003 123372037853 -223372035855 12345679900.1234000000 22345679900.1234000000 1.12345674897976E10 -1.12345674897976E10 1000

      ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      As you can see the values of the columns have been switched.

      ::::: AFTER ASCII OPERATION ::::SPARK 1.6::::::

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      cust_id cust_name active_emui_version dob doj bigint_column1 bigint_column2 decimal_column1 decimal_column2 double_column1 double_column2 integer_column1

      -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      10801 67 ACTIVE_EMUI_VERSION_01801 1974-12-07 01:00:03.0 1974-12-07 02:00:03.0 123372038655 -223372035053 12345680702.1234000000 22345680702.1234000000 1.12345674897976E10 -1.12345674897976E10 1802
      10802 67 ACTIVE_EMUI_VERSION_01802 1974-12-08 01:00:03.0 1974-12-08 02:00:03.0 123372038656 -223372035052 12345680703.1234000000 22345680703.1234000000 1.12345674897976E10 -1.12345674897976E10 1803
      10803 67 ACTIVE_EMUI_VERSION_01803 1974-12-09 01:00:03.0 1974-12-09 02:00:03.0 123372038657 -223372035051 12345680704.1234000000 22345680704.1234000000 1.12345674897976E10 -1.12345674897976E10 1804
      10804 67 ACTIVE_EMUI_VERSION_01804 1974-12-10 01:00:03.0 1974-12-10 02:00:03.0 123372038658 -223372035050 12345680705.1234000000 22345680705.1234000000 1.12345674897976E10 -1.12345674897976E10 1805
      10805 67 ACTIVE_EMUI_VERSION_01805 1974-12-11 01:00:03.0 1974-12-11 02:00:03.0 123372038659 -223372035049 12345680706.1234000000 22345680706.1234000000 1.12345674897976E10 -1.12345674897976E10 1806
      10806 67 ACTIVE_EMUI_VERSION_01806 1974-12-12 01:00:03.0 1974-12-12 02:00:03.0 123372038660 -223372035048 12345680707.1234000000 22345680707.1234000000 1.12345674897976E10 -1.12345674897976E10 1807
      10807 67 ACTIVE_EMUI_VERSION_01807 1974-12-13 01:00:03.0 1974-12-13 02:00:03.0 123372038661 -223372035047 12345680708.1234000000 22345680708.1234000000 1.12345674897976E10 -1.12345674897976E10 1808
      10808 67 ACTIVE_EMUI_VERSION_01808 1974-12-14 01:00:03.0 1974-12-14 02:00:03.0 123372038662 -223372035046 12345680709.1234000000 22345680709.1234000000 1.12345674897976E10 -1.12345674897976E10 1809
      10809 67 ACTIVE_EMUI_VERSION_01809 1974-12-15 01:00:03.0 1974-12-15 02:00:03.0 123372038663 -223372035045 12345680710.1234000000 22345680710.1234000000 1.12345674897976E10 -1.12345674897976E10 1810
      10810 67 ACTIVE_EMUI_VERSION_01810 1974-12-16 01:00:03.0 1974-12-16 02:00:03.0 123372038664 -223372035044 12345680711.1234000000 22345680711.1234000000 1.12345674897976E10 -1.12345674897976E10 1811
      10811 67 ACTIVE_EMUI_VERSION_01811 1974-12-17 01:00:03.0 1974-12-17 02:00:03.0 123372038665 -223372035043 12345680712.1234000000 22345680712.1234000000 1.12345674897976E10 -1.12345674897976E10 1812
      10812 67 ACTIVE_EMUI_VERSION_01812 1974-12-18 01:00:03.0 1974-12-18 02:00:03.0 123372038666 -223372035042 12345680713.1234000000 22345680713.1234000000 1.12345674897976E10 -1.12345674897976E10 1813
      10813 67 ACTIVE_EMUI_VERSION_01813 1974-12-19 01:00:03.0 1974-12-19 02:00:03.0 123372038667 -223372035041 12345680714.1234000000 22345680714.1234000000 1.12345674897976E10 -1.12345674897976E10 1814

      Here, its working in Spark 1.6

      Attachments

        1. Driver Logs
          25 kB
          Harsh Sharma
        2. 2000_UniqData.csv
          416 kB
          Harsh Sharma

        Activity

          People

            ravi.pesala Ravindra Pesala
            harshsharma8 Harsh Sharma
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: