Project error query records

The saiku data query result is incorrect, which is three times the source data in hive.

Problem locating:

Problem with mdx executed by saiku

Select non empty {[Measures]. [Downloads]} on columns,

Non empty filter (CrossJoin ([appname. default]. [appname]. members, CrossJoin ([developer. default]. [developer]. members, [version. default]. [version]. members), [packagename. default]. [packagename]. members), [packagename. default]. [packagename]. currentmember is [packagename. default]. [packagename]. [com. tencent. mm]) ON ROWS

FROM [aso] WHERE ([OS. default]. [OS]. [1], [dimStoreName. default]. [storeName]. [all], [dimdate. default]. [day]. [])

The execution result is incorrect, which is three times that of hive data.

 

Therefore, go to modroin_mdx.log and modroin_ SQL .log to find the corresponding execution statement,

Run the tail-n 200 filename command to find the corresponding execution statement.

(During the Query Process, too many statements are executed, so the two files are deleted and saiku is restarted. However, the statements that have been executed will be cached by saiku. I couldn't find the package name. Later I found the package name again in hive and executed the new statement)

 

Run the SQL statement. The sum function is used because group by completes a group.

Select

'Dimdate'. 'Year' as 'c0 ',

'Diddate'. 'month' as 'c1 ',

'Dimdate'. 'datevalue 'as 'c2 ',

'Dimappstatic '. 'packagename' as 'c3 ',

Sum ('factrank'. 'primarytaxonomyrank _ week ') as 'm0'

From 'dimdate' as 'dimdate', 'factrank' as 'factrank', 'dimappstatic 'as 'dimappstatic'

Where

'Factrank'. 'dt' = 'dimdate'. 'datevalue'

And 'dimdate'. 'Year' = '000000' and 'diddate'. 'month' in ('1', '2 ')

And 'dimdate '. 'datevale' in ('1970-01-06 ', '1970-01-13', '1970-01-20 ', '1970-01-27', '1970-02-04 ', '2017-02-10 ', '2017-02-17', '2017-02-24 ')

And 'factrank'. 'pk _ hash' = 'dimappstatic '. 'pk _ hash'

And 'dimappstatic '. 'packagename' = 'com. tencent. mm'

Group by 'dimdate'. 'Year', 'dimdate'. 'month', 'dimdate'. 'datevalue ', 'dimappstatic'. 'packagename'

 

Delete the row and sum function of the group by statement, retain only one row, and check that the query result is three repeated columns of data. This indicates that three columns of duplicate data exist in the associated table.

The SQL query result of infiniDB is repeated in three columns. This indicates that a Data repeat three times and is finally set to the appstatic table.

Cause Analysis: mysql uses kettle for import, and insert/update can remove duplicate columns. In infiniDB, the load command is used and shell is used to execute the command without verifying the repeatability. So it was executed three times. An error occurred.