private function generateCopyCommand($tempTableName, $columns, CsvFile $csvFile, array $options) { $tableNameEscaped = $this->tableNameEscaped($tempTableName); $columnsSql = implode(', ', array_map(function ($column) { return $this->quoteIdentifier($column); }, $columns)); $command = "COPY {$tableNameEscaped} ({$columnsSql}) " . " FROM {$this->connection->quote($csvFile->getPathname())}" . " CREDENTIALS 'aws_access_key_id={$this->s3key};aws_secret_access_key={$this->s3secret}' " . " DELIMITER '{$csvFile->getDelimiter()}' "; if ($csvFile->getEnclosure()) { $command .= "QUOTE {$this->connection->quote($csvFile->getEnclosure())} "; } if ($csvFile->getEscapedBy()) { // raw format if ($csvFile->getEscapedBy() != '\\') { throw new Exception('Only backshlash can be used as escape character'); } $command .= " ESCAPE "; } else { $command .= " CSV "; } if (!empty($options['isGzipped'])) { $command .= " GZIP "; } if (!empty($options['isManifest'])) { $command .= " MANIFEST "; } $command .= " IGNOREHEADER " . $this->getIgnoreLines(); // custom options $command .= " " . implode(" ", $options['copyOptions']); return $command; }
private function importFile($stagingTableName, CsvFile $csvFile) { $files = $this->getFilesToDownloadFromManifest($csvFile->getPathname()); foreach ($files as $path) { $newCsvPath = new CsvFile($path, $csvFile->getDelimiter(), $csvFile->getEnclosure(), $csvFile->getEscapedBy()); $this->importTable($stagingTableName, $newCsvPath); } }
private function generateCopyCommand($tableName, CsvFile $csvFile) { $csvOptions = []; $csvOptions[] = sprintf('FIELD_DELIMITER = %s', $this->quote($csvFile->getDelimiter())); if ($this->getIgnoreLines()) { $csvOptions[] = sprintf('SKIP_HEADER = %d', $this->getIgnoreLines()); } if ($csvFile->getEnclosure()) { $csvOptions[] = sprintf("FIELD_OPTIONALLY_ENCLOSED_BY = %s", $this->quote($csvFile->getEnclosure())); $csvOptions[] = "ESCAPE_UNENCLOSED_FIELD = NONE"; } elseif ($csvFile->getEscapedBy()) { $csvOptions[] = sprintf("ESCAPE_UNENCLOSED_FIELD = %s", $this->quote($csvFile->getEscapedBy())); } $command = sprintf("COPY INTO %s FROM %s \n CREDENTIALS = (AWS_KEY_ID = %s AWS_SECRET_KEY = %s)\n REGION = %s\n FILE_FORMAT = (TYPE=CSV %s)", $this->nameWithSchemaEscaped($tableName), $this->quote($csvFile->getPathname()), $this->quote($this->s3key), $this->quote($this->s3secret), $this->quote($this->s3region), implode(' ', $csvOptions)); return $command; }
public function run($options = null) { $accounts = $this->configuration->getAccounts(); $status = array(); $dateFrom = isset($options['since']) ? date('Y-m-d', strtotime($options['since'])) : date('Y-m-d', strtotime('-4 days')); $dateTo = isset($options['until']) ? date('Y-m-d', strtotime($options['until'])) : date('Y-m-d', strtotime('-1 day')); $dataset = isset($options['dataset']) ? $options['dataset'] : null; if (isset($options['account'])) { if (!isset($accounts[$options['account']])) { throw new UserException(sprintf("Account '%s' does not exist.", $options['account'])); } $accounts = array($options['account'] => $accounts[$options['account']]); } if (isset($options['config'])) { if (!isset($accounts[$options['config']])) { throw new UserException(sprintf("Config '%s' does not exist.", $options['config'])); } $accounts = array($options['config'] => $accounts[$options['config']]); } if ($dataset != null && !isset($options['config']) && !isset($options['account'])) { throw new UserException("Missing parameter 'config'"); } /** @var Account $account */ foreach ($accounts as $accountId => $account) { // check if account has been authorized if (null == $account->getAttribute('accessToken')) { continue; } $this->currAccountId = $accountId; if (null == $account->getAttribute('outputBucket')) { $this->configuration->initDataBucket($account->getAccountId()); } else { if (!$this->configuration->getStorageApi()->bucketExists($account->getAttribute('outputBucket'))) { $outBucketArr = explode('.', $account->getAttribute('outputBucket')); $this->configuration->getStorageApi()->createBucket(str_replace('c-', '', $outBucketArr[1]), $outBucketArr[0], 'Google Analytics data bucket'); } } $this->gaApi->getApi()->setCredentials($account->getAccessToken(), $account->getRefreshToken()); $this->gaApi->getApi()->setRefreshTokenCallback(array($this, 'refreshTokenCallback')); $tmpFileInfo = $this->temp->createFile("profiles-" . $accountId . "-" . microtime() . ".csv"); $profilesCsv = new CsvFile($tmpFileInfo->getPathname()); $profilesCsv->writeRow(array('id', 'name')); /** @var Profile $profile */ foreach ($account->getProfiles() as $profile) { $profilesCsv->writeRow(array($profile->getGoogleId(), $profile->getName())); try { $configuration = $account->getConfiguration(); // Download just the dataset specified in request if ($dataset != null) { if (!isset($configuration[$dataset])) { throw new UserException(sprintf("Dataset '%s' doesn't exist", $dataset)); } $configuration = [$dataset => $configuration[$dataset]]; } foreach ($configuration as $tableName => $cfg) { // Download dataset only for specific profile if (isset($cfg['profile']) && $cfg['profile'] != $profile->getGoogleId()) { continue; } $antisampling = isset($cfg['antisampling']) ? $cfg['antisampling'] : false; $this->getData($account, $profile, $tableName, $dateFrom, $dateTo, $antisampling); $status[$accountId][$profile->getName()][$tableName] = 'ok'; } } catch (RequestException $e) { if ($e->getCode() == 401) { throw new UserException("Expired or wrong credentials, please reauthorize.", $e); } if ($e->getCode() == 403) { if (strtolower($e->getResponse()->getReasonPhrase()) == 'forbidden') { $this->logger->warning("You don't have access to Google Analytics resource. Probably you don't have access to profile, or profile doesn't exists anymore."); continue; } else { throw new UserException("Reason: " . $e->getResponse()->getReasonPhrase(), $e); } } if ($e->getCode() == 400) { throw new UserException($e->getMessage()); } if ($e->getCode() == 503) { throw new UserException("Google API error: " . $e->getMessage(), $e); } throw new ApplicationException($e->getResponse()->getBody(), $e); } } $this->getDataManager()->uploadCsv($profilesCsv->getPathname(), $this->getOutputTable($account, 'profiles')); } return $status; }