Details
-
Bug
-
Status: Closed
-
Major
-
Resolution: Won't Fix
-
None
-
None
-
None
Description
=> 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 -----------
--------------------------------------------------