private List<AnalyticsTableColumn> getValueColumns() { return Lists.newArrayList( new AnalyticsTableColumn( quote( "value" ), "date", "value" ) ); }
/** * Filters out analytics table columns which were created * after the time of the last successful resource table update. * * @param columns the analytics table columns. * @return a list of {@link AnalyticsTableColumn}. */ protected List<AnalyticsTableColumn> filterDimensionColumns( List<AnalyticsTableColumn> columns ) { Date lastResourceTableUpdate = (Date) systemSettingManager.getSystemSetting( SettingKey.LAST_SUCCESSFUL_RESOURCE_TABLES_UPDATE ); if ( lastResourceTableUpdate == null ) { return columns; } return columns.stream() .filter( c -> c.getCreated() == null || c.getCreated().before( lastResourceTableUpdate ) ) .collect( Collectors.toList() ); }
/** * 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 ); }
/** * 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 ); }
/** * Checks whether the given list of columns are valid. * * @param columns the list of {@link AnalyticsTableColumn}. * @throws IllegalStateException if not valid. */ protected void validateDimensionColumns( List<AnalyticsTableColumn> columns ) { if ( columns == null || columns.isEmpty() ) { throw new IllegalStateException( "Analytics table dimensions are empty" ); } List<String> columnNames = columns.stream().map( d -> d.getName() ).collect( Collectors.toList() ); Set<String> duplicates = ListUtils.getDuplicates( columnNames ); if ( !duplicates.isEmpty() ) { throw new IllegalStateException( "Analytics table dimensions contain duplicates: " + duplicates ); } }
@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 ); }
private List<AnalyticsTableColumn> getValueColumns() { return Lists.newArrayList( new AnalyticsTableColumn( quote( "value" ), "date", "value" ) ); }
private List<AnalyticsTableColumn> getValueColumns() { final String dbl = statementBuilder.getDoubleColumnType(); return Lists.newArrayList( new AnalyticsTableColumn( quote( "daysxvalue" ), dbl, "daysxvalue" ), new AnalyticsTableColumn( quote( "daysno" ), "integer not null", "daysno" ), new AnalyticsTableColumn( quote( "value" ), dbl, "value" ), new AnalyticsTableColumn( quote( "textvalue" ), "text", "textvalue" ) ); }
private List<AnalyticsTableColumn> getValueColumns() { final String dbl = statementBuilder.getDoubleColumnType(); return Lists.newArrayList( new AnalyticsTableColumn( quote( "value" ), dbl, "value" ) ); }
private List<AnalyticsTableColumn> getValueColumns() { final String dbl = statementBuilder.getDoubleColumnType(); return Lists.newArrayList( new AnalyticsTableColumn( quote( "value" ), dbl, "value" ) ); }
private List<AnalyticsTableColumn> getDimensionColumns() { List<AnalyticsTableColumn> columns = new ArrayList<>(); List<OrganisationUnitLevel> levels = organisationUnitService.getFilledOrganisationUnitLevels(); for ( OrganisationUnitLevel level : levels ) { String column = quote( PREFIX_ORGUNITLEVEL + level.getLevel() ); columns.add( new AnalyticsTableColumn( column, "character(11)", "ous." + column, level.getCreated() ) ); } columns.add( new AnalyticsTableColumn( quote( "oug" ), "character(11) not null", "oug.uid" ) ); return filterDimensionColumns( columns ); }
columns.add( new AnalyticsTableColumn( quote( groupSet.getUid() ), "character(11)", "ougs." + quote( groupSet.getUid() ), groupSet.getCreated() ) ); columns.add( new AnalyticsTableColumn( column, "character(11)", "ous." + column, level.getCreated() ) ); columns.add( new AnalyticsTableColumn( quote( category.getUid() ), "character(11)", "acs." + quote( category.getUid() ), category.getCreated() ) ); columns.add( new AnalyticsTableColumn( column, "text", "ps." + column ) ); columns.add( new AnalyticsTableColumn( quote( "dx" ), "character(11) not null", "vr.uid" ) ); columns.add( new AnalyticsTableColumn( quote( "pestartdate" ), "timestamp", "pe.startdate" ) ); columns.add( new AnalyticsTableColumn( quote( "peenddate" ), "timestamp", "pe.enddate" ) ); columns.add( new AnalyticsTableColumn( quote( "year" ), "integer not null", "ps.year" ) );
columns.add( new AnalyticsTableColumn( quote( groupSet.getUid() ), "character(11)", "ougs." + quote( groupSet.getUid() ), groupSet.getCreated() ) ); columns.add( new AnalyticsTableColumn( column, "character(11)", "ous." + column, level.getCreated() ) ); columns.add( new AnalyticsTableColumn( quote( groupSet.getUid() ), "character(11)", "acs." + quote( groupSet.getUid() ), groupSet.getCreated() ) ); columns.add( new AnalyticsTableColumn( quote( category.getUid() ), "character(11)", "acs." + quote( category.getUid() ), category.getCreated() ) ); columns.add( new AnalyticsTableColumn( quote( "ouopeningdate"), "date", "ou.openingdate" ) ); columns.add( new AnalyticsTableColumn( quote( "oucloseddate"), "date", "ou.closeddate" ) ); columns.add( new AnalyticsTableColumn( quote( "costartdate" ), "date", "doc.costartdate" ) ); columns.add( new AnalyticsTableColumn( quote( "coenddate" ), "date", "doc.coenddate" ) ); columns.add( new AnalyticsTableColumn( quote( "dx" ), "character(11) not null", "ds.uid" ) ); columns.add( new AnalyticsTableColumn( quote( "ao" ), "character(11) not null", "ao.uid" ) );
columns.add( new AnalyticsTableColumn( column, "character(11)", "ous." + column, level.getCreated() ) ); columns.add( new AnalyticsTableColumn( quote( groupSet.getUid() ), "character(11)", "ougs." + quote( groupSet.getUid() ), groupSet.getCreated() ) ); columns.add( new AnalyticsTableColumn( column, "text", "dps." + column ) ); columns.add( new AnalyticsTableColumn( quote( attribute.getUid() ), dataType, sql, skipIndex ) ); columns.add( new AnalyticsTableColumn( quote( "pi" ), "character(11) not null", "pi.uid" ) ); columns.add( new AnalyticsTableColumn( quote( "enrollmentdate" ), "timestamp", "pi.enrollmentdate" ) ); columns.add( new AnalyticsTableColumn( quote( "incidentdate" ), "timestamp", "pi.incidentdate" ) ); "order by psi.executiondate desc " + "limit 1) as " + quote( "executiondate" ); columns.add( new AnalyticsTableColumn( quote( "executiondate" ), "timestamp", executionDateSql ) ); "order by psi.duedate desc " + "limit 1) as " + quote( "duedate" ); columns.add( new AnalyticsTableColumn( quote( "duedate" ), "timestamp", dueDateSql ) ); columns.add( new AnalyticsTableColumn( quote( "completeddate" ), "timestamp", "case pi.status when 'COMPLETED' then pi.enddate end" ) ); columns.add( new AnalyticsTableColumn( quote( "enrollmentstatus" ), "character(50)", "pi.status" ) ); columns.add( new AnalyticsTableColumn( quote( "longitude" ), dbl, "ST_X(pi.geometry)" ) ); columns.add( new AnalyticsTableColumn( quote( "latitude" ), dbl, "ST_Y(pi.geometry)" ) ); columns.add( new AnalyticsTableColumn( quote( "ou" ), "character(11) not null", "ou.uid" ) ); columns.add( new AnalyticsTableColumn( quote( "ouname" ), "text not null", "ou.name" ) ); columns.add( new AnalyticsTableColumn( quote( "oucode" ), "text", "ou.code" ) ); columns.add( new AnalyticsTableColumn( quote( "pigeometry" ), "geometry", "pi.geometry", false, "gist" ) );