[
https://issues.apache.org/jira/browse/CARBONDATA-442?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
SWATI RAO closed CARBONDATA-442.
--------------------------------
> Query result mismatching with Hive
> ----------------------------------
>
> Key: CARBONDATA-442
> URL: https://issues.apache.org/jira/browse/CARBONDATA-442
> Project: CarbonData
> Issue Type: Bug
> Reporter: SWATI RAO
> Fix For: 1.0.0-incubating
>
>
> => I created table using following command :
> create table Carbon_automation_test5 (imei string,deviceInformationId int,MAC
> string,deviceColor string,device_backColor string,modelId string,marketName
> string,AMSize string,ROMSize string,CUPAudit string,CPIClocked string,series
> string,productionDate string,bomCode string,internalModels string,
> deliveryTime string, channelsId string,channelsName string , deliveryAreaId
> string, deliveryCountry string, deliveryProvince string, deliveryCity
> string,deliveryDistrict string, deliveryStreet string,oxSingleNumber string,
> ActiveCheckTime string, ActiveAreaId string, ActiveCountry string,
> ActiveProvince string, Activecity string, ActiveDistrict string, ActiveStreet
> string, ActiveOperatorId string, Active_releaseId string, Active_EMUIVersion
> string,Active_operaSysVersion string, Active_BacVerNumber string,
> Active_BacFlashVer string,Active_webUIVersion string, Active_webUITypeCarrVer
> string,Active_webTypeDataVerNumber string, Active_operatorsVersion string,
> Active_phonePADPartitionedVersions string,Latest_YEAR int, Latest_MONTH int,
> Latest_DAY int, Latest_HOUR string, Latest_areaId string, Latest_country
> string, Latest_province string, Latest_city string,Latest_district string,
> Latest_street string, Latest_releaseId string,Latest_EMUIVersion string,
> Latest_operaSysVersion string, Latest_BacVerNumber string,Latest_BacFlashVer
> string, Latest_webUIVersion string, Latest_webUITypeCarrVer
> string,Latest_webTypeDataVerNumber string, Latest_operatorsVersion
> string,Latest_phonePADPartitionedVersions string, Latest_operatorId
> string,gamePointDescription string, gamePointId int,contractNumber int)
> stored by 'org.apache.carbondata.format'
> => Load csv to table :
> LOAD DATA INPATH 'hdfs://localhost:54310/user/hduser/100_olap.csv' INTO table
> Carbon_automation_test5 OPTIONS('DELIMITER'= ',' ,'QUOTECHAR'= '"',
> 'FILEHEADER'=
> 'imei,deviceInformationId,MAC,deviceColor,device_backColor,modelId,marketName,AMSize,ROMSize,CUPAudit,CPIClocked,series,productionDate,bomCode,internalModels,deliveryTime,channelsId,channelsName,deliveryAreaId,deliveryCountry,deliveryProvince,deliveryCity,deliveryDistrict,deliveryStreet,oxSingleNumber,contractNumber,ActiveCheckTime,ActiveAreaId,ActiveCountry,ActiveProvince,Activecity,ActiveDistrict,ActiveStreet,ActiveOperatorId,Active_releaseId,Active_EMUIVersion,Active_operaSysVersion,Active_BacVerNumber,Active_BacFlashVer,Active_webUIVersion,Active_webUITypeCarrVer,Active_webTypeDataVerNumber,Active_operatorsVersion,Active_phonePADPartitionedVersions,Latest_YEAR,Latest_MONTH,Latest_DAY,Latest_HOUR,Latest_areaId,Latest_country,Latest_province,Latest_city,Latest_district,Latest_street,Latest_releaseId,Latest_EMUIVersion,Latest_operaSysVersion,Latest_BacVerNumber,Latest_BacFlashVer,Latest_webUIVersion,Latest_webUITypeCarrVer,Latest_webTypeDataVerNumber,Latest_operatorsVersion,Latest_phonePADPartitionedVersions,Latest_operatorId,gamePointId,gamePointDescription')
> =>now executed SELECT querry :
> SELECT Carbon_automation_test5.AMSize AS AMSize,
> Carbon_automation_test5.ActiveCountry AS ActiveCountry,
> Carbon_automation_test5.Activecity AS Activecity ,
> SUM(Carbon_automation_test5.gamePointId) AS Sum_gamePointId FROM ( SELECT
> AMSize,ActiveCountry,gamePointId, Activecity FROM (select * from
> Carbon_automation_test5) SUB_QRY ) Carbon_automation_test5 INNER JOIN (
> SELECT ActiveCountry, Activecity, AMSize FROM (select * from
> Carbon_automation_test5) SUB_QRY ) Carbon_automation_vmall_test1 ON
> Carbon_automation_test5.AMSize = Carbon_automation_vmall_test1.AMSize WHERE
> NOT(Carbon_automation_test5.AMSize <= '3RAM size') GROUP BY
> Carbon_automation_test5.AMSize, Carbon_automation_test5.ActiveCountry,
> Carbon_automation_test5.Activecity ORDER BY Carbon_automation_test5.AMSize
> ASC, Carbon_automation_test5.ActiveCountry ASC,
> Carbon_automation_test5.Activecity ASC;
> +------------+----------------+-------------+------------------+--+
> | AMSize | ActiveCountry | Activecity | Sum_gamePointId |
> +------------+----------------+-------------+------------------+--+
> | 4RAM size | Chinese | changsha | 200860 |
> | 4RAM size | Chinese | guangzhou | 38016 |
> | 4RAM size | Chinese | shenzhen | 49610 |
> | 4RAM size | Chinese | wuhan | 117568 |
> | 4RAM size | Chinese | xiangtan | 254320 |
> | 4RAM size | Chinese | yichang | 29436 |
> | 5RAM size | Chinese | changsha | 13845 |
> | 5RAM size | Chinese | guangzhou | 23560 |
> | 5RAM size | Chinese | wuhan | 12390 |
> | 6RAM size | Chinese | changsha | 23697 |
> | 6RAM size | Chinese | guangzhou | 15912 |
> | 6RAM size | Chinese | shenzhen | 19278 |
> | 6RAM size | Chinese | wuhan | 29313 |
> | 6RAM size | Chinese | xiangtan | 7794 |
> | 6RAM size | Chinese | zhuzhou | 26568 |
> | 7RAM size | Chinese | changsha | 1057 |
> | 7RAM size | Chinese | wuhan | 27853 |
> | 7RAM size | Chinese | yichang | 14217 |
> | 7RAM size | Chinese | zhuzhou | 15673 |
> | 8RAM size | Chinese | guangzhou | 27380 |
> | 8RAM size | Chinese | shenzhen | 3550 |
> | 8RAM size | Chinese | wuhan | 29700 |
> | 8RAM size | Chinese | xiangtan | 31020 |
> | 8RAM size | Chinese | yichang | 51660 |
> | 8RAM size | Chinese | zhuzhou | 26840 |
> | 9RAM size | Chinese | changsha | 32390 |
> | 9RAM size | Chinese | shenzhen | 30650 |
> | 9RAM size | Chinese | wuhan | 15670 |
> | 9RAM size | Chinese | xiangtan | 58210 |
> | 9RAM size | Chinese | yichang | 5710 |
> +------------+----------------+-------------+------------------+--+
> => Also i have executed above command on hive as follow :
> # create table Carbon_automation_test5_hive (imei string,deviceInformationId
> int,MAC string,deviceColor string,device_backColor string,modelId
> string,marketName string,AMSize string,ROMSize string,CUPAudit
> string,CPIClocked string,series string,productionDate string,bomCode
> string,internalModels string, deliveryTime string, channelsId
> string,channelsName string , deliveryAreaId string, deliveryCountry string,
> deliveryProvince string, deliveryCity string,deliveryDistrict string,
> deliveryStreet string,oxSingleNumber string, ActiveCheckTime string,
> ActiveAreaId string, ActiveCountry string, ActiveProvince string, Activecity
> string, ActiveDistrict string, ActiveStreet string, ActiveOperatorId string,
> Active_releaseId string, Active_EMUIVersion string,Active_operaSysVersion
> string, Active_BacVerNumber string, Active_BacFlashVer
> string,Active_webUIVersion string, Active_webUITypeCarrVer
> string,Active_webTypeDataVerNumber string, Active_operatorsVersion string,
> Active_phonePADPartitionedVersions string,Latest_YEAR int, Latest_MONTH int,
> Latest_DAY int, Latest_HOUR string, Latest_areaId string, Latest_country
> string, Latest_province string, Latest_city string,Latest_district string,
> Latest_street string, Latest_releaseId string,Latest_EMUIVersion string,
> Latest_operaSysVersion string, Latest_BacVerNumber string,Latest_BacFlashVer
> string, Latest_webUIVersion string, Latest_webUITypeCarrVer
> string,Latest_webTypeDataVerNumber string, Latest_operatorsVersion
> string,Latest_phonePADPartitionedVersions string, Latest_operatorId
> string,gamePointDescription string, gamePointId int,contractNumber int) row
> format delimited fields terminated by ','
> # LOAD DATA INPATH 'hdfs://localhost:54310/user/hduser/100_olap.csv' INTO
> table Carbon_automation_test5;
> # SELECT Carbon_automation_test5_hive.AMSize AS AMSize,
> Carbon_automation_test5_hive.ActiveCountry AS ActiveCountry,
> Carbon_automation_test5_hive.Activecity AS Activecity ,
> SUM(Carbon_automation_test5_hive.gamePointId) AS Sum_gamePointId FROM (
> SELECT AMSize,ActiveCountry,gamePointId, Activecity FROM (select * from
> Carbon_automation_test5_hive) SUB_QRY ) Carbon_automation_test5_hive INNER
> JOIN ( SELECT ActiveCountry, Activecity, AMSize FROM (select * from
> Carbon_automation_test5_hive) SUB_QRY ) Carbon_automation_vmall_test1 ON
> Carbon_automation_test5_hive.AMSize = Carbon_automation_vmall_test1.AMSize
> WHERE NOT(Carbon_automation_test5_hive.AMSize <= '3RAM size') GROUP BY
> Carbon_automation_test5_hive.AMSize,
> Carbon_automation_test5_hive.ActiveCountry,
> Carbon_automation_test5_hive.Activecity ORDER BY
> Carbon_automation_test5_hive.AMSize ASC,
> Carbon_automation_test5_hive.ActiveCountry ASC,
> Carbon_automation_test5_hive.Activecity ASC;
> +------------+----------------+---------------------+------------------+--+
> | AMSize | ActiveCountry | Activecity | Sum_gamePointId |
> +------------+----------------+---------------------+------------------+--+
> | 4RAM size | 1 | Guangdong Province | 49610 |
> | 4RAM size | 2 | Guangdong Province | 38016 |
> | 4RAM size | 3 | Hunan Province | 200860 |
> | 4RAM size | 4 | Hunan Province | 254320 |
> | 4RAM size | 6 | Hubei Province | 117568 |
> | 4RAM size | 7 | Hubei Province | 29436 |
> | 5RAM size | 2 | Guangdong Province | 23560 |
> | 5RAM size | 3 | Hunan Province | 13845 |
> | 5RAM size | 6 | Hubei Province | 12390 |
> | 6RAM size | 1 | Guangdong Province | 19278 |
> | 6RAM size | 2 | Guangdong Province | 15912 |
> | 6RAM size | 3 | Hunan Province | 23697 |
> | 6RAM size | 4 | Hunan Province | 7794 |
> | 6RAM size | 5 | Hunan Province | 26568 |
> | 6RAM size | 6 | Hubei Province | 29313 |
> | 7RAM size | 3 | Hunan Province | 1057 |
> | 7RAM size | 5 | Hunan Province | 15673 |
> | 7RAM size | 6 | Hubei Province | 27853 |
> | 7RAM size | 7 | Hubei Province | 14217 |
> | 8RAM size | 1 | Guangdong Province | 3550 |
> | 8RAM size | 2 | Guangdong Province | 27380 |
> | 8RAM size | 4 | Hunan Province | 31020 |
> | 8RAM size | 5 | Hunan Province | 26840 |
> | 8RAM size | 6 | Hubei Province | 29700 |
> | 8RAM size | 7 | Hubei Province | 51660 |
> | 9RAM size | 1 | Guangdong Province | 30650 |
> | 9RAM size | 3 | Hunan Province | 32390 |
> | 9RAM size | 4 | Hunan Province | 58210 |
> | 9RAM size | 6 | Hubei Province | 15670 |
> | 9RAM size | 7 | Hubei Province | 5710 |
> +------------+----------------+---------------------+------------------+--+
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
