/**
* Performs merge operation according to http://docs.aws.amazon.com/redshift/latest/dg/merge-specify-a-column-list.html
* @param $stagingTableName
* @param $targetTableName
* @param $columns
*/
private function insertOrUpdateTargetTable($stagingTableName, $targetTableName, $columns, $useTimestamp = true)
{
$this->connection->query('BEGIN TRANSACTION');
$nowFormatted = $this->getNowFormatted();
$targetTableNameWithSchema = $this->nameWithSchemaEscaped($targetTableName);
$stagingTableNameWithSchema = $this->nameWithSchemaEscaped($stagingTableName);
$primaryKey = $this->connection->getTablePrimaryKey($this->schemaName, $targetTableName);
if (!empty($primaryKey)) {
// Update target table
$sql = "UPDATE " . $targetTableNameWithSchema . " AS \"dest\" SET ";
$columnsSet = [];
foreach ($columns as $columnName) {
$columnsSet[] = sprintf('%s = COALESCE("src".%s, \'\')', $this->quoteIdentifier($columnName), $this->quoteIdentifier($columnName));
}
$sql .= implode(', ', $columnsSet);
if ($useTimestamp) {
$sql .= ", " . $this->quoteIdentifier(self::TIMESTAMP_COLUMN_NAME) . " = '{$nowFormatted}' ";
}
$sql .= " FROM " . $stagingTableNameWithSchema . ' AS "src" ';
$sql .= " WHERE ";
$pkWhereSql = [];
foreach ($primaryKey as $pkColumn) {
$pkWhereSql[] = sprintf('"dest".%s = "src".%s', $this->quoteIdentifier($pkColumn), $this->quoteIdentifier($pkColumn));
}
$sql .= implode(' AND ', $pkWhereSql) . " ";
// update only changed rows - mysql TIMESTAMP ON UPDATE behaviour simulation
$columnsComparsionSql = array_map(function ($columnName) {
return sprintf('"dest".%s != COALESCE("src".%s, \'\')', $this->quoteIdentifier($columnName), $this->quoteIdentifier($columnName));
}, $columns);
$sql .= " AND (" . implode(' OR ', $columnsComparsionSql) . ") ";
Debugger::timer('updateTargetTable');
$this->connection->query($sql);
$this->addTimer('updateTargetTable', Debugger::timer('updateTargetTable'));
// Delete updated rows from staging table
$sql = "DELETE FROM " . $stagingTableNameWithSchema . ' "src" ';
$sql .= "USING " . $targetTableNameWithSchema . ' AS "dest" ';
$sql .= "WHERE " . implode(' AND ', $pkWhereSql);
Debugger::timer('deleteUpdatedRowsFromStaging');
$this->connection->query($sql);
$this->addTimer('deleteUpdatedRowsFromStaging', Debugger::timer('deleteUpdatedRowsFromStaging'));
// Dedup staging table
Debugger::timer('dedupStaging');
$this->dedupe($stagingTableName, $columns, $primaryKey);
$this->addTimer('dedupStaging', Debugger::timer('dedupStaging'));
}
// Insert from staging to target table
$insColumns = $useTimestamp ? array_merge($columns, [self::TIMESTAMP_COLUMN_NAME]) : $columns;
$sql = "INSERT INTO " . $targetTableNameWithSchema . ' (' . implode(', ', array_map(function ($column) {
return $this->quoteIdentifier($column);
}, $insColumns)) . ")";
$columnsSetSql = [];
foreach ($columns as $columnName) {
$columnsSetSql[] = sprintf('COALESCE("src".%s, \'\')', $this->quoteIdentifier($columnName));
}
$sql .= " SELECT " . implode(',', $columnsSetSql);
if ($useTimestamp) {
$sql .= ", '{$nowFormatted}' ";
}
$sql .= "FROM " . $stagingTableNameWithSchema . ' AS "src"';
Debugger::timer('insertIntoTargetFromStaging');
$this->connection->query($sql);
$this->addTimer('insertIntoTargetFromStaging', Debugger::timer('insertIntoTargetFromStaging'));
$this->connection->query('COMMIT');
}
public function testGetPrimaryKey()
{
$pk = $this->connection->getTablePrimaryKey($this->destSchemaName, 'accounts-3');
$this->assertEquals(['id'], $pk);
}
