@Override @Transactional public List<AnalyticsTable> getAnalyticsTables( Date earliest ) { return Lists.newArrayList( new AnalyticsTable( getTableName(), getDimensionColumns(), getValueColumns() ) ); }
public String getTableName() { String name = masterTable.getBaseName(); if ( year != null ) { name += PartitionUtils.SEP + year; } if ( masterTable.getProgram() != null ) { name += PartitionUtils.SEP + masterTable.getProgram().getUid().toLowerCase(); } return name; }
/** * 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 ); }
/** * Returns a list of table partitions based on the given analytics tables. For * master tables with no partitions, a fake partition representing the master * table is used. * * @param tables the list of {@link AnalyticsTable}. * @return a list of {@link AnalyticsTablePartition}. */ public static List<AnalyticsTablePartition> getTablePartitions( List<AnalyticsTable> tables ) { final List<AnalyticsTablePartition> partitions = Lists.newArrayList(); for ( AnalyticsTable table : tables ) { if ( table.hasPartitionTables() ) { partitions.addAll( table.getPartitionTables() ); } else { // Fake partition representing the master table partitions.add( new AnalyticsTablePartition( table, null, null, null, false ) ); } } return partitions; }
@Override public void swapTable( AnalyticsTableUpdateParams params, AnalyticsTable table ) { boolean tableExists = partitionManager.tableExists( table.getTableName() ); boolean skipMasterTable = params.isPartialUpdate() && tableExists; log.info( String.format( "Swapping table, master table exists: %b, skip master table: %b", tableExists, skipMasterTable ) ); table.getPartitionTables().stream().forEach( p -> swapTable( p.getTempTableName(), p.getTableName() ) ); if ( !skipMasterTable ) { swapTable( table.getTempTableName(), table.getTableName() ); } else { table.getPartitionTables().stream().forEach( p -> swapInheritance( p.getTableName(),table.getTempTableName(), table.getTableName() ) ); dropTempTable( table ); } }
@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 ); }
@Override @Transactional public List<AnalyticsTable> getAnalyticsTables( Date earliest ) { log.info( String.format( "Get tables using earliest: %s, spatial support: %b", earliest, databaseInfo.isSpatialSupport() ) ); List<AnalyticsTable> tables = new ArrayList<>(); Calendar calendar = PeriodType.getCalendar(); String baseName = getTableName(); List<Program> programs = idObjectManager.getAllNoAcl( Program.class ); for ( Program program : programs ) { List<Integer> dataYears = getDataYears( program, earliest ); Collections.sort( dataYears ); AnalyticsTable table = new AnalyticsTable( baseName, getDimensionColumns( program ), Lists.newArrayList(), program ); for ( Integer year : dataYears ) { table.addPartitionTable( year, PartitionUtils.getStartDate( calendar, year ), PartitionUtils.getEndDate( calendar, year ) ); } if ( table.hasPartitionTables() ) { tables.add( table ); } } return tables; }
/** * Drops and creates the table partitions for the given analytics table. * * @param table the {@link AnalyticsTable}. */ protected void createTempTablePartitions( AnalyticsTable table ) { for ( AnalyticsTablePartition partition : table.getPartitionTables() ) { final String tableName = partition.getTempTableName(); final List<String> checks = getPartitionChecks( partition ); String sqlCreate = "create table " + tableName + " "; if ( !checks.isEmpty() ) { StringBuilder sqlCheck = new StringBuilder( "(" ); checks.stream().forEach( check -> sqlCheck.append( "check (" + check + "), " ) ); sqlCreate += TextUtils.removeLastComma( sqlCheck.toString() ) + ") "; } sqlCreate += "inherits (" + table.getTempTableName() + ") " + getTableOptions(); log.info( String.format( "Creating partition table: %s", tableName ) ); log.debug( "Create SQL: " + sqlCreate ); jdbcTemplate.execute( sqlCreate ); } }
/** * Generates a list of {@link AnalyticsTable} based on a list of years with data. * * @param dataYears the list of years with data. * @param dimensionColumns the list of dimension {@link AnalyticsTableColumn}. * @param valueColumns the list of value {@link AnalyticsTableColumn}. */ protected AnalyticsTable getAnalyticsTable( List<Integer> dataYears, List<AnalyticsTableColumn> dimensionColumns, List<AnalyticsTableColumn> valueColumns ) { Calendar calendar = PeriodType.getCalendar(); Collections.sort( dataYears ); String baseName = getAnalyticsTableType().getTableName(); AnalyticsTable table = new AnalyticsTable( baseName, dimensionColumns, valueColumns ); for ( Integer year : dataYears ) { table.addPartitionTable( year, PartitionUtils.getStartDate( calendar, year ), PartitionUtils.getEndDate( calendar, year ) ); } return table; }
@Override @Transactional public List<AnalyticsTable> getAnalyticsTables( Date earliest ) { AnalyticsTable table = getAnalyticsTable( getDataYears( earliest ), getDimensionColumns(), getValueColumns() ); return table.hasPartitionTables() ? Lists.newArrayList( table ) : Lists.newArrayList(); }
@Override protected void populateTable( AnalyticsTableUpdateParams params, AnalyticsTablePartition partition ) final Program program = partition.getMasterTable().getProgram(); final String tableName = partition.getTempTableName();
@Override public String toString() { return "[Table name: " + getTableName() + ", partitions: " + partitionTables + "]"; } }
@Override public void dropTempTable( AnalyticsTable table ) { dropTableCascade( table.getTempTableName() ); }
/** * 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 ); }
List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns();
@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 ); }
@Override @Transactional public List<AnalyticsTable> getAnalyticsTables( Date earliest ) { AnalyticsTable table = getAnalyticsTable( getDataYears( earliest ), getDimensionColumns( null ), getValueColumns() ); return table.hasPartitionTables() ? newArrayList( table ) : newArrayList(); }
@Override protected void populateTable( AnalyticsTableUpdateParams params, AnalyticsTablePartition partition ) final Program program = partition.getMasterTable().getProgram(); final String start = DateUtils.getMediumDateString( partition.getStartDate() ); final String end = DateUtils.getMediumDateString( partition.getEndDate() );
List<AnalyticsTableColumn> columns = partition.getMasterTable().getDimensionColumns(); List<AnalyticsTableColumn> values = partition.getMasterTable().getValueColumns();
public String getTempTableName() { String name = masterTable.getBaseName() + AnalyticsTableManager.TABLE_TEMP_SUFFIX; if ( year != null ) { name += PartitionUtils.SEP + year; } if ( masterTable.getProgram() != null ) { name += PartitionUtils.SEP + masterTable.getProgram().getUid().toLowerCase(); } return name; }