/** * Drops and creates the given analytics table. * * @param table the {@link AnalyticsTable}. */ protected void createTempTable( AnalyticsTable table ) { validateDimensionColumns( table.getDimensionColumns() ); final String tableName = table.getTempTableName(); String sqlCreate = "create table " + tableName + " ("; for ( AnalyticsTableColumn col : ListUtils.union( table.getDimensionColumns(), table.getValueColumns() ) ) { sqlCreate += col.getName() + " " + col.getDataType() + ","; } sqlCreate = TextUtils.removeLastComma( sqlCreate ) + ") " + getTableOptions(); log.info( String.format( "Creating table: %s, columns: %d", tableName, table.getDimensionColumns().size() ) ); log.debug( "Create SQL: " + sqlCreate ); jdbcTemplate.execute( sqlCreate ); }
/** * Creates indexes on the given analytics tables. * * @param tables the list of {@link AnalyticsTable}. */ private void createIndexes( List<AnalyticsTable> tables ) { List<AnalyticsTablePartition> partitions = PartitionUtils.getTablePartitions( tables ); ConcurrentLinkedQueue<AnalyticsIndex> indexes = new ConcurrentLinkedQueue<>(); for ( AnalyticsTablePartition partition : partitions ) { List<AnalyticsTableColumn> columns = partition.getMasterTable().getDimensionColumns(); for ( AnalyticsTableColumn col : columns ) { if ( !col.isSkipIndex() ) { List<String> indexColumns = col.hasIndexColumns() ? col.getIndexColumns() : Lists.newArrayList( col.getName() ); indexes.add( new AnalyticsIndex( partition.getTempTableName(), indexColumns, col.getIndexType() ) ); } } } log.info( "No of analytics table indexes: " + indexes.size() ); List<Future<?>> futures = new ArrayList<>(); for ( int i = 0; i < getProcessNo(); i++ ) { futures.add( tableManager.createIndexesAsync( indexes ) ); } ConcurrentUtils.waitForCompletion( futures ); }
@Override protected void populateTable( AnalyticsTableUpdateParams params, AnalyticsTablePartition partition ) { final String tableName = partition.getTempTableName(); String sql = "insert into " + tableName + " ("; List<AnalyticsTableColumn> columns = partition.getMasterTable().getDimensionColumns(); List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns(); validateDimensionColumns( columns ); for ( AnalyticsTableColumn col : ListUtils.union( columns, values ) ) { sql += col.getName() + ","; } sql = TextUtils.removeLastComma( sql ) + ") select "; for ( AnalyticsTableColumn col : columns ) { sql += col.getAlias() + ","; } sql += "1 as value " + "from _datasetorganisationunitcategory doc " + "inner join dataset ds on doc.datasetid=ds.datasetid " + "inner join organisationunit ou on doc.organisationunitid=ou.organisationunitid " + "left join _orgunitstructure ous on doc.organisationunitid=ous.organisationunitid " + "left join _organisationunitgroupsetstructure ougs on doc.organisationunitid=ougs.organisationunitid " + "left join categoryoptioncombo ao on doc.attributeoptioncomboid=ao.categoryoptioncomboid " + "left join _categorystructure acs on doc.attributeoptioncomboid=acs.categoryoptioncomboid "; populateAndLog( sql, tableName ); }
List<AnalyticsTableColumn> columns = partition.getMasterTable().getDimensionColumns(); List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns();
List<AnalyticsTableColumn> columns = partition.getMasterTable().getDimensionColumns(); List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns();
@Override protected void populateTable( AnalyticsTableUpdateParams params, AnalyticsTablePartition partition ) { final String tableName = partition.getTempTableName(); String sql = "insert into " + partition.getTempTableName() + " ("; List<AnalyticsTableColumn> columns = partition.getMasterTable().getDimensionColumns(); List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns(); validateDimensionColumns( columns ); for ( AnalyticsTableColumn col : ListUtils.union( columns, values ) ) { sql += col.getName() + ","; } sql = TextUtils.removeLastComma( sql ) + ") select "; for ( AnalyticsTableColumn col : columns ) { sql += col.getAlias() + ","; } sql += "1 as value " + "from orgunitgroupmembers ougm " + "inner join orgunitgroup oug on ougm.orgunitgroupid=oug.orgunitgroupid " + "left join _orgunitstructure ous on ougm.organisationunitid=ous.organisationunitid " + "left join _organisationunitgroupsetstructure ougs on ougm.organisationunitid=ougs.organisationunitid"; populateAndLog( sql, tableName ); }