You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@druid.apache.org by "JWuCines (via GitHub)" <gi...@apache.org> on 2026/07/16 13:42:42 UTC
[PR] feat: Support PostgreSQL export for export-metadata (druid)
JWuCines opened a new pull request, #19698:
URL: https://github.com/apache/druid/pull/19698
### Description
The `export-metadata` tool currently only supports exporting from Derby metadata stores. This PR adds support for exporting from PostgreSQL by implementing a generic JDBC-based `exportTable` in `SQLMetadataConnector`, which PostgreSQL (and any future connector) inherits automatically.
#### Added generic JDBC export in `SQLMetadataConnector`
Implemented `exportTableWithJdbc`, a `protected` method that exports any table to CSV using standard JDBC. Binary/BLOB columns (including PostgreSQL `BYTEA`) are hex-encoded, booleans are written as `true`/`false` strings, and string values containing commas, quotes, `\n`, or `\r` are properly CSV-escaped per RFC 4180. The base `exportTable` delegates to this method, while `DerbyConnector` continues to override it with Derby's native `SYSCS_EXPORT_TABLE`.
#### Updated `ExportMetadata` for PostgreSQL compatibility
- Table names are no longer unconditionally uppercased; uppercasing is now applied only for Derby (detected via `jdbc:derby` URI prefix), since PostgreSQL uses lowercase table names.
- Updated the `@Command` description to mention PostgreSQL support.
#### Added unit tests
Four new tests in `SQLMetadataConnectorTest` exercise the generic JDBC export path via `TestDerbyConnector.exportTableGeneric()`:
- **`testExportTable`** — verifies hex-encoded BLOBs and `true`/`false` boolean strings
- **`testExportTableWithSpecialCharacters`** — verifies CSV quoting/escaping for commas, double quotes, and plain values
- **`testExportTableWithNullValues`** — verifies NULL columns produce empty CSV fields
- **`testExportTablePreservesAllColumns`** — verifies all columns (including nullable trailing columns like `used_status_last_updated`) are exported
#### Updated documentation
- **`export-metadata.md`** — removed the Derby-only limitation, added a "PostgreSQL" section under "Running the tool" with the required `-Ddruid.extensions.loadList` and `-Ddruid.metadata.storage.type` flags, clarified `_raw.csv` description
- **`metadata-migration.md`** — updated intro and export tool reference to include PostgreSQL
- **`deep-storage-migration.md`** — updated export tool reference, added note about no running processes needed when migrating from PostgreSQL
#### Release note
The `export-metadata` tool now supports exporting from PostgreSQL metadata stores in addition to Derby. When exporting from PostgreSQL, pass `-Ddruid.extensions.loadList='["postgresql-metadata-storage"]' -Ddruid.metadata.storage.type=postgresql` on the command line along with the appropriate `--connectURI`.
<hr>
##### Key changed/added classes in this PR
* `SQLMetadataConnector` — added `exportTable` and `exportTableWithJdbc` for generic JDBC CSV export
* `ExportMetadata` — added `isDerby()` helper, conditional table name casing
* `TestDerbyConnector` — added `exportTableGeneric()` to test the generic JDBC path
* `SQLMetadataConnectorTest` — added 4 export tests
<hr>
This PR has:
- [x] been self-reviewed.
- [x] added documentation for new or modified features or behaviors.
- [x] a release note entry in the PR description.
- [x] added Javadocs for most classes and all non-trivial methods. Linked related entities via Javadoc links.
- [x] added comments explaining the "why" and the intent of the code wherever would not be obvious for an unfamiliar reader.
- [x] added unit tests or modified existing tests to cover new code paths, ensuring the threshold for code coverage is met.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3712426826
##########
docs/operations/export-metadata.md:
##########
@@ -151,14 +164,18 @@ In the example command above:
After running the tool, the output directory will contain `<table-name>_raw.csv` and `<table-name>.csv` files.
-The `<table-name>_raw.csv` files are intermediate files used by the tool, containing the table data as exported by Derby without modification.
+The `<table-name>_raw.csv` files are intermediate files used by the tool, containing the table data as exported from the source database without deep-storage rewrites. BLOB columns are hex-encoded and booleans are written as `true`/`false` strings.
The `<table-name>.csv` files are used for import into another database such as MySQL and PostgreSQL and have any configured deep storage location rewrites applied.
Example import commands for Derby, MySQL, and PostgreSQL are shown below.
These example import commands expect `/tmp/csv` and its contents to be accessible from the server. For other options, such as importing from the client filesystem, please refer to the database's documentation.
+The segments table is exported in a fixed column order, independent of the physical column order of the source table: `id`, `dataSource`, `created_date`, `start`, `end`, `partitioned`, `version`, `used`, `payload`, followed by whichever of the optional columns `used_status_last_updated`, `indexing_state_fingerprint`, `upgraded_from_segment_id`, `schema_fingerprint`, and `num_rows` exist in the source table, in that order. Adjust the segments column list in the import commands below to contain exactly the columns of the source table: omit any optional column the source table does not have (segments tables from older Druid versions may have only the first nine columns), and add `schema_fingerprint,num_rows` at the end if the source table has them. Apply the same adjustment to the columns declared with `FORCE_NULL` in the PostgreSQL command.
Review Comment:
[P1] Provide a column-mapped Derby import for legacy exports
The new guidance says to omit optional columns from the import list, but the Derby example uses SYSCS_IMPORT_TABLE, which has no column list and imports every destination column in physical order. A nine-column legacy export therefore still cannot be loaded into a current 12/14-column Derby segments table, and the documented adjustment cannot be performed. Use SYSCS_IMPORT_DATA with an explicit mapping, or provide another working legacy-schema procedure.
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,206 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema of the current connection, which is the schema an unqualified
+ * table name resolves to. The table name is folded to the case in which the database stores
+ * unquoted identifiers, and escaped so that it is matched literally rather than as a
+ * {@link DatabaseMetaData#getColumns} search pattern.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ final DatabaseMetaData dbMetaData = conn.getMetaData();
+ try (ResultSet rs = dbMetaData.getColumns(
+ null,
+ escapeMetaDataSearchString(dbMetaData, conn.getSchema()),
Review Comment:
[P2] Look up columns in the configured PostgreSQL schema
PostgreSQLConnector.tableExists scopes the relation to druid.metadata.postgres.dbTableSchema, but getTableColumns scopes DatabaseMetaData.getColumns to Connection.getSchema(). These can differ, for example when a role-named schema precedes public in search_path while the Druid tables remain in public, so tableExists succeeds but column discovery returns empty and exportSegmentsTable aborts. Use the connector's configured table schema or resolve the actual schema containing the unqualified relation.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "JWuCines (via GitHub)" <gi...@apache.org>.
JWuCines commented on PR #19698:
URL: https://github.com/apache/druid/pull/19698#issuecomment-5450877383
I will create a following PR for the improvement of exporting data tooling!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3740843249
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1054,6 +1061,196 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema returned by {@link #getMetadataTableSchema(Connection)}, which is the schema
+ * an unqualified table name resolves to. The table name is folded to the case in which the database stores unquoted
+ * identifiers, since {@link DatabaseMetaData#getColumns} patterns are case-sensitive.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ final DatabaseMetaData dbMetaData = conn.getMetaData();
+ final String storedName = foldIdentifierCase(dbMetaData, tableName);
+ try (ResultSet rs = dbMetaData.getColumns(null, getMetadataTableSchema(conn), storedName, null)) {
+ while (rs.next()) {
+ // '_' is a wildcard in the table name pattern, so match the returned table name exactly
+ if (storedName.equals(rs.getString("TABLE_NAME"))) {
+ columns.add(rs.getString("COLUMN_NAME"));
+ }
+ }
+ }
+ }
+ }
+ catch (SQLException e) {
+ log.warn(e, "Could not read columns of table[%s].", tableName);
+ }
+ return columns;
+ });
+ }
+
+ /**
+ * Returns the schema that the Druid metadata tables live in, i.e. the schema that an unqualified
+ * table name in a Druid SQL statement resolves to, or null if the schema is unknown and lookups
+ * should not be scoped to a schema.
+ *
+ * Connectors that scope {@link #tableExists} to a configured schema must override this so that
+ * both lookups agree.
+ */
+ @Nullable
+ protected String getMetadataTableSchema(final Connection connection) throws SQLException
+ {
+ return connection.getSchema();
+ }
+
+ /**
+ * Folds the given identifier to the case in which the database stores unquoted identifiers.
+ * {@link DatabaseMetaData} lookup patterns are case-sensitive, while an unquoted identifier
+ * in a SQL statement is folded by the database (to lowercase in PostgreSQL, to uppercase in
+ * Derby), so the folded form must be used to match the table a SQL reference resolves to.
+ */
+ private static String foldIdentifierCase(
+ final DatabaseMetaData dbMetaData,
+ final String identifier
+ ) throws SQLException
+ {
+ if (dbMetaData.storesLowerCaseIdentifiers()) {
+ return StringUtils.toLowerCase(identifier);
+ }
+ if (dbMetaData.storesUpperCaseIdentifiers()) {
+ return StringUtils.toUpperCase(identifier);
+ }
+ return identifier;
+ }
+
+ /**
+ * Builds the select list for an export query, quoting each column with the database's
+ * identifier quote string so that reserved words such as "end" are handled correctly.
+ */
+ protected String makeExportSelectList(final Connection conn, final List<String> columns) throws SQLException
+ {
+ final String quote = conn.getMetaData().getIdentifierQuoteString();
+ if (quote == null || " ".equals(quote)) {
+ return String.join(",", columns);
+ }
+ return columns.stream()
+ .map(column -> quote + StringUtils.replace(column, quote, quote + quote) + quote)
+ .collect(Collectors.joining(","));
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ // Use a transaction so that the connection has autoCommit=false.
+ // PostgreSQL JDBC requires autoCommit=false and a positive fetch size
+ // to use cursor-based streaming instead of buffering the entire ResultSet.
+ retryTransaction(
+ (TransactionCallback<Void>) (handle, status) -> {
+ final Connection conn = handle.getConnection();
+ final String selectList = columns == null || columns.isEmpty() ? "*" : makeExportSelectList(conn, columns);
+ try (Statement stmt = conn.createStatement()) {
+ final int fetchSize = getStreamingFetchSize();
+ if (fetchSize > 0) {
+ stmt.setFetchSize(fetchSize);
+ }
+ try (ResultSet rs = stmt.executeQuery(StringUtils.format("SELECT %s FROM %s", selectList, tableName));
Review Comment:
[P1] Export ignores configured PostgreSQL schema
With a non-public dbTableSchema, column discovery uses the configured schema but this query remains unqualified. PostgreSQL may fail to find the table or export a same-named table from another schema. Resolve and quote the configured schema/table consistently.
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1054,6 +1061,196 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema returned by {@link #getMetadataTableSchema(Connection)}, which is the schema
+ * an unqualified table name resolves to. The table name is folded to the case in which the database stores unquoted
+ * identifiers, since {@link DatabaseMetaData#getColumns} patterns are case-sensitive.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ final DatabaseMetaData dbMetaData = conn.getMetaData();
+ final String storedName = foldIdentifierCase(dbMetaData, tableName);
+ try (ResultSet rs = dbMetaData.getColumns(null, getMetadataTableSchema(conn), storedName, null)) {
+ while (rs.next()) {
+ // '_' is a wildcard in the table name pattern, so match the returned table name exactly
+ if (storedName.equals(rs.getString("TABLE_NAME"))) {
+ columns.add(rs.getString("COLUMN_NAME"));
+ }
+ }
+ }
+ }
+ }
+ catch (SQLException e) {
+ log.warn(e, "Could not read columns of table[%s].", tableName);
+ }
+ return columns;
+ });
+ }
+
+ /**
+ * Returns the schema that the Druid metadata tables live in, i.e. the schema that an unqualified
+ * table name in a Druid SQL statement resolves to, or null if the schema is unknown and lookups
+ * should not be scoped to a schema.
+ *
+ * Connectors that scope {@link #tableExists} to a configured schema must override this so that
+ * both lookups agree.
+ */
+ @Nullable
+ protected String getMetadataTableSchema(final Connection connection) throws SQLException
+ {
+ return connection.getSchema();
+ }
+
+ /**
+ * Folds the given identifier to the case in which the database stores unquoted identifiers.
+ * {@link DatabaseMetaData} lookup patterns are case-sensitive, while an unquoted identifier
+ * in a SQL statement is folded by the database (to lowercase in PostgreSQL, to uppercase in
+ * Derby), so the folded form must be used to match the table a SQL reference resolves to.
+ */
+ private static String foldIdentifierCase(
+ final DatabaseMetaData dbMetaData,
+ final String identifier
+ ) throws SQLException
+ {
+ if (dbMetaData.storesLowerCaseIdentifiers()) {
+ return StringUtils.toLowerCase(identifier);
+ }
+ if (dbMetaData.storesUpperCaseIdentifiers()) {
+ return StringUtils.toUpperCase(identifier);
+ }
+ return identifier;
+ }
+
+ /**
+ * Builds the select list for an export query, quoting each column with the database's
+ * identifier quote string so that reserved words such as "end" are handled correctly.
+ */
+ protected String makeExportSelectList(final Connection conn, final List<String> columns) throws SQLException
+ {
+ final String quote = conn.getMetaData().getIdentifierQuoteString();
+ if (quote == null || " ".equals(quote)) {
+ return String.join(",", columns);
+ }
+ return columns.stream()
+ .map(column -> quote + StringUtils.replace(column, quote, quote + quote) + quote)
+ .collect(Collectors.joining(","));
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ // Use a transaction so that the connection has autoCommit=false.
+ // PostgreSQL JDBC requires autoCommit=false and a positive fetch size
+ // to use cursor-based streaming instead of buffering the entire ResultSet.
+ retryTransaction(
+ (TransactionCallback<Void>) (handle, status) -> {
+ final Connection conn = handle.getConnection();
+ final String selectList = columns == null || columns.isEmpty() ? "*" : makeExportSelectList(conn, columns);
+ try (Statement stmt = conn.createStatement()) {
+ final int fetchSize = getStreamingFetchSize();
+ if (fetchSize > 0) {
+ stmt.setFetchSize(fetchSize);
+ }
+ try (ResultSet rs = stmt.executeQuery(StringUtils.format("SELECT %s FROM %s", selectList, tableName));
+ OutputStreamWriter writer =
+ new OutputStreamWriter(new FileOutputStream(outputPath), StandardCharsets.UTF_8)) {
+ final int columnCount = rs.getMetaData().getColumnCount();
+ final List<String> values = new ArrayList<>(columnCount);
+ while (rs.next()) {
+ values.clear();
+ for (int i = 1; i <= columnCount; i++) {
+ values.add(readCsvValue(rs, i));
+ }
+ writer.write(String.join(",", values));
+ writer.write('\n');
+ }
+ }
+ }
+ return null;
+ },
+ QUIET_RETRIES,
+ DEFAULT_MAX_TRIES
+ );
+ }
+
+ /**
+ * Reads the given column of the current row as a CSV field. Binary values are hex-encoded, booleans are written
+ * as true/false and NULLs as empty fields.
+ */
+ private static String readCsvValue(final ResultSet rs, final int column) throws SQLException
+ {
+ final ResultSetMetaData meta = rs.getMetaData();
+ final int type = meta.getColumnType(column);
+ if (type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY || type == Types.BLOB
+ || (type == Types.OTHER && "bytea".equalsIgnoreCase(meta.getColumnTypeName(column)))) {
+ final byte[] bytes = rs.getBytes(column);
+ return bytes == null ? "" : BaseEncoding.base16().encode(bytes);
+ } else if (type == Types.BOOLEAN || type == Types.BIT) {
+ final boolean value = rs.getBoolean(column);
+ return rs.wasNull() ? "" : String.valueOf(value);
+ } else {
+ return csvEscape(rs.getString(column));
Review Comment:
[P1] Nullable num_rows cannot be imported safely
A nullable num_rows is serialized as an empty field. The Derby and MySQL commands documented for adding this column do not convert empty input to SQL NULL: Derby rejects it and MySQL may coerce it to zero. Add equivalent NULL handling for those import paths.
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1054,6 +1061,196 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema returned by {@link #getMetadataTableSchema(Connection)}, which is the schema
+ * an unqualified table name resolves to. The table name is folded to the case in which the database stores unquoted
+ * identifiers, since {@link DatabaseMetaData#getColumns} patterns are case-sensitive.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ final DatabaseMetaData dbMetaData = conn.getMetaData();
+ final String storedName = foldIdentifierCase(dbMetaData, tableName);
+ try (ResultSet rs = dbMetaData.getColumns(null, getMetadataTableSchema(conn), storedName, null)) {
+ while (rs.next()) {
+ // '_' is a wildcard in the table name pattern, so match the returned table name exactly
+ if (storedName.equals(rs.getString("TABLE_NAME"))) {
+ columns.add(rs.getString("COLUMN_NAME"));
+ }
+ }
+ }
+ }
+ }
+ catch (SQLException e) {
+ log.warn(e, "Could not read columns of table[%s].", tableName);
+ }
+ return columns;
+ });
+ }
+
+ /**
+ * Returns the schema that the Druid metadata tables live in, i.e. the schema that an unqualified
+ * table name in a Druid SQL statement resolves to, or null if the schema is unknown and lookups
+ * should not be scoped to a schema.
+ *
+ * Connectors that scope {@link #tableExists} to a configured schema must override this so that
+ * both lookups agree.
+ */
+ @Nullable
+ protected String getMetadataTableSchema(final Connection connection) throws SQLException
+ {
+ return connection.getSchema();
+ }
+
+ /**
+ * Folds the given identifier to the case in which the database stores unquoted identifiers.
+ * {@link DatabaseMetaData} lookup patterns are case-sensitive, while an unquoted identifier
+ * in a SQL statement is folded by the database (to lowercase in PostgreSQL, to uppercase in
+ * Derby), so the folded form must be used to match the table a SQL reference resolves to.
+ */
+ private static String foldIdentifierCase(
+ final DatabaseMetaData dbMetaData,
+ final String identifier
+ ) throws SQLException
+ {
+ if (dbMetaData.storesLowerCaseIdentifiers()) {
+ return StringUtils.toLowerCase(identifier);
+ }
+ if (dbMetaData.storesUpperCaseIdentifiers()) {
+ return StringUtils.toUpperCase(identifier);
+ }
+ return identifier;
+ }
+
+ /**
+ * Builds the select list for an export query, quoting each column with the database's
+ * identifier quote string so that reserved words such as "end" are handled correctly.
+ */
+ protected String makeExportSelectList(final Connection conn, final List<String> columns) throws SQLException
+ {
+ final String quote = conn.getMetaData().getIdentifierQuoteString();
+ if (quote == null || " ".equals(quote)) {
+ return String.join(",", columns);
+ }
+ return columns.stream()
+ .map(column -> quote + StringUtils.replace(column, quote, quote + quote) + quote)
+ .collect(Collectors.joining(","));
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ // Use a transaction so that the connection has autoCommit=false.
+ // PostgreSQL JDBC requires autoCommit=false and a positive fetch size
+ // to use cursor-based streaming instead of buffering the entire ResultSet.
+ retryTransaction(
+ (TransactionCallback<Void>) (handle, status) -> {
+ final Connection conn = handle.getConnection();
+ final String selectList = columns == null || columns.isEmpty() ? "*" : makeExportSelectList(conn, columns);
+ try (Statement stmt = conn.createStatement()) {
+ final int fetchSize = getStreamingFetchSize();
+ if (fetchSize > 0) {
+ stmt.setFetchSize(fetchSize);
+ }
+ try (ResultSet rs = stmt.executeQuery(StringUtils.format("SELECT %s FROM %s", selectList, tableName));
+ OutputStreamWriter writer =
+ new OutputStreamWriter(new FileOutputStream(outputPath), StandardCharsets.UTF_8)) {
+ final int columnCount = rs.getMetaData().getColumnCount();
+ final List<String> values = new ArrayList<>(columnCount);
+ while (rs.next()) {
+ values.clear();
+ for (int i = 1; i <= columnCount; i++) {
+ values.add(readCsvValue(rs, i));
+ }
+ writer.write(String.join(",", values));
+ writer.write('\n');
+ }
+ }
+ }
+ return null;
+ },
+ QUIET_RETRIES,
+ DEFAULT_MAX_TRIES
+ );
+ }
+
+ /**
+ * Reads the given column of the current row as a CSV field. Binary values are hex-encoded, booleans are written
+ * as true/false and NULLs as empty fields.
+ */
+ private static String readCsvValue(final ResultSet rs, final int column) throws SQLException
+ {
+ final ResultSetMetaData meta = rs.getMetaData();
+ final int type = meta.getColumnType(column);
+ if (type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY || type == Types.BLOB
+ || (type == Types.OTHER && "bytea".equalsIgnoreCase(meta.getColumnTypeName(column)))) {
+ final byte[] bytes = rs.getBytes(column);
+ return bytes == null ? "" : BaseEncoding.base16().encode(bytes);
+ } else if (type == Types.BOOLEAN || type == Types.BIT) {
+ final boolean value = rs.getBoolean(column);
+ return rs.wasNull() ? "" : String.valueOf(value);
+ } else {
+ return csvEscape(rs.getString(column));
+ }
+ }
+
+ /**
+ * Escapes a value for CSV output as per RFC 4180: values containing a comma, double quote or line break are
+ * wrapped in double quotes, with inner double quotes doubled. A null value is written as an empty field.
+ */
+ public static String csvEscape(@Nullable final String value)
Review Comment:
[P2] MySQL import consumes backslashes
The RFC CSV writer leaves backslashes literal, while the documented MySQL LOAD DATA command uses MySQL's default backslash escape processing. Values such as JSON backslash-n or segment identifiers containing backslashes are therefore altered during PostgreSQL-to-MySQL migration. Use matching escape semantics in the writer and import command.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "JWuCines (via GitHub)" <gi...@apache.org>.
JWuCines commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3602925286
##########
server/src/test/java/org/apache/druid/metadata/SQLMetadataConnectorTest.java:
##########
@@ -463,6 +468,219 @@
);
}
+ @Test
+ public void testExportTable() throws IOException
+ {
+ final String tableName = "test_export";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB NOT NULL, active BOOLEAN NOT NULL, PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key1",
+ StringUtils.toUtf8("{\"type\":\"test\"}"),
+ true
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key2",
+ StringUtils.toUtf8("{\"value\":42}"),
+ false
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_test", ".csv");
+ outputFile.deleteOnExit();
+
+ // Call the base class exportTable (the generic JDBC path used by PostgreSQL)
+ // rather than DerbyConnector's native SYSCS_EXPORT_TABLE override
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
Review Comment:
[Use Files.createTempFile instead of File.createTempFile for owner-only permissions](https://github.com/apache/druid/pull/19698/changes/c2d66e690e011db4ed9c3017c5b4cabd950779f4)
##########
server/src/test/java/org/apache/druid/metadata/SQLMetadataConnectorTest.java:
##########
@@ -463,6 +468,219 @@
);
}
+ @Test
+ public void testExportTable() throws IOException
+ {
+ final String tableName = "test_export";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB NOT NULL, active BOOLEAN NOT NULL, PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key1",
+ StringUtils.toUtf8("{\"type\":\"test\"}"),
+ true
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key2",
+ StringUtils.toUtf8("{\"value\":42}"),
+ false
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_test", ".csv");
+ outputFile.deleteOnExit();
+
+ // Call the base class exportTable (the generic JDBC path used by PostgreSQL)
+ // rather than DerbyConnector's native SYSCS_EXPORT_TABLE override
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(2, lines.size());
+ Collections.sort(lines);
+
+ // Verify rows (sorted by name): hex-encoded payload, boolean as string
+ final String expectedHex1 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"type\":\"test\"}"));
+ Assert.assertEquals("key1," + expectedHex1 + ",true", lines.get(0));
+
+ final String expectedHex2 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"value\":42}"));
+ Assert.assertEquals("key2," + expectedHex2 + ",false", lines.get(1));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithSpecialCharacters() throws IOException
+ {
+ final String tableName = "test_export_special";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, description VARCHAR(1024), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "commas",
+ "value,with,commas"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "quotes",
+ "value\"with\"quotes"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "simple",
+ "plain_value"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_special_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
Review Comment:
[Use Files.createTempFile instead of File.createTempFile for owner-only permissions](https://github.com/apache/druid/pull/19698/changes/c2d66e690e011db4ed9c3017c5b4cabd950779f4)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "github-advanced-security[bot] (via GitHub)" <gi...@apache.org>.
github-advanced-security[bot] commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3596285142
##########
server/src/test/java/org/apache/druid/metadata/SQLMetadataConnectorTest.java:
##########
@@ -463,6 +468,219 @@
);
}
+ @Test
+ public void testExportTable() throws IOException
+ {
+ final String tableName = "test_export";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB NOT NULL, active BOOLEAN NOT NULL, PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key1",
+ StringUtils.toUtf8("{\"type\":\"test\"}"),
+ true
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key2",
+ StringUtils.toUtf8("{\"value\":42}"),
+ false
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_test", ".csv");
+ outputFile.deleteOnExit();
+
+ // Call the base class exportTable (the generic JDBC path used by PostgreSQL)
+ // rather than DerbyConnector's native SYSCS_EXPORT_TABLE override
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
Review Comment:
## CodeQL / Local information disclosure in a temporary directory
Local information disclosure vulnerability due to use of file readable by other local users.
[Show more details](https://github.com/apache/druid/security/code-scanning/11344)
##########
server/src/test/java/org/apache/druid/metadata/SQLMetadataConnectorTest.java:
##########
@@ -463,6 +468,219 @@
);
}
+ @Test
+ public void testExportTable() throws IOException
+ {
+ final String tableName = "test_export";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB NOT NULL, active BOOLEAN NOT NULL, PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key1",
+ StringUtils.toUtf8("{\"type\":\"test\"}"),
+ true
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key2",
+ StringUtils.toUtf8("{\"value\":42}"),
+ false
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_test", ".csv");
+ outputFile.deleteOnExit();
+
+ // Call the base class exportTable (the generic JDBC path used by PostgreSQL)
+ // rather than DerbyConnector's native SYSCS_EXPORT_TABLE override
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(2, lines.size());
+ Collections.sort(lines);
+
+ // Verify rows (sorted by name): hex-encoded payload, boolean as string
+ final String expectedHex1 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"type\":\"test\"}"));
+ Assert.assertEquals("key1," + expectedHex1 + ",true", lines.get(0));
+
+ final String expectedHex2 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"value\":42}"));
+ Assert.assertEquals("key2," + expectedHex2 + ",false", lines.get(1));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithSpecialCharacters() throws IOException
+ {
+ final String tableName = "test_export_special";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, description VARCHAR(1024), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "commas",
+ "value,with,commas"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "quotes",
+ "value\"with\"quotes"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "simple",
+ "plain_value"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_special_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
Review Comment:
## CodeQL / Local information disclosure in a temporary directory
Local information disclosure vulnerability due to use of file readable by other local users.
[Show more details](https://github.com/apache/druid/security/code-scanning/11345)
##########
server/src/test/java/org/apache/druid/metadata/SQLMetadataConnectorTest.java:
##########
@@ -463,6 +468,219 @@
);
}
+ @Test
+ public void testExportTable() throws IOException
+ {
+ final String tableName = "test_export";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB NOT NULL, active BOOLEAN NOT NULL, PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key1",
+ StringUtils.toUtf8("{\"type\":\"test\"}"),
+ true
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key2",
+ StringUtils.toUtf8("{\"value\":42}"),
+ false
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_test", ".csv");
+ outputFile.deleteOnExit();
+
+ // Call the base class exportTable (the generic JDBC path used by PostgreSQL)
+ // rather than DerbyConnector's native SYSCS_EXPORT_TABLE override
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(2, lines.size());
+ Collections.sort(lines);
+
+ // Verify rows (sorted by name): hex-encoded payload, boolean as string
+ final String expectedHex1 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"type\":\"test\"}"));
+ Assert.assertEquals("key1," + expectedHex1 + ",true", lines.get(0));
+
+ final String expectedHex2 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"value\":42}"));
+ Assert.assertEquals("key2," + expectedHex2 + ",false", lines.get(1));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithSpecialCharacters() throws IOException
+ {
+ final String tableName = "test_export_special";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, description VARCHAR(1024), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "commas",
+ "value,with,commas"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "quotes",
+ "value\"with\"quotes"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "simple",
+ "plain_value"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_special_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(3, lines.size());
+ Collections.sort(lines);
+
+ // Values with commas should be quoted (sorted order: commas, quotes, simple)
+ Assert.assertEquals("commas,\"value,with,commas\"", lines.get(0));
+ // Values with quotes should be quoted and quotes doubled
+ Assert.assertEquals("quotes,\"value\"\"with\"\"quotes\"", lines.get(1));
+ // Simple values should not be quoted
+ Assert.assertEquals("simple,plain_value", lines.get(2));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithNullValues() throws IOException
+ {
+ final String tableName = "test_export_nulls";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB, description VARCHAR(255), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "with_values",
+ StringUtils.toUtf8("{\"key\":1}"),
+ "has_desc"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s (name) VALUES (?)", tableName),
+ "null_cols"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_nulls_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
Review Comment:
## CodeQL / Local information disclosure in a temporary directory
Local information disclosure vulnerability due to use of file readable by other local users.
[Show more details](https://github.com/apache/druid/security/code-scanning/11346)
##########
server/src/test/java/org/apache/druid/metadata/SQLMetadataConnectorTest.java:
##########
@@ -463,6 +468,219 @@
);
}
+ @Test
+ public void testExportTable() throws IOException
+ {
+ final String tableName = "test_export";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB NOT NULL, active BOOLEAN NOT NULL, PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key1",
+ StringUtils.toUtf8("{\"type\":\"test\"}"),
+ true
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key2",
+ StringUtils.toUtf8("{\"value\":42}"),
+ false
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_test", ".csv");
+ outputFile.deleteOnExit();
+
+ // Call the base class exportTable (the generic JDBC path used by PostgreSQL)
+ // rather than DerbyConnector's native SYSCS_EXPORT_TABLE override
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(2, lines.size());
+ Collections.sort(lines);
+
+ // Verify rows (sorted by name): hex-encoded payload, boolean as string
+ final String expectedHex1 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"type\":\"test\"}"));
+ Assert.assertEquals("key1," + expectedHex1 + ",true", lines.get(0));
+
+ final String expectedHex2 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"value\":42}"));
+ Assert.assertEquals("key2," + expectedHex2 + ",false", lines.get(1));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithSpecialCharacters() throws IOException
+ {
+ final String tableName = "test_export_special";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, description VARCHAR(1024), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "commas",
+ "value,with,commas"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "quotes",
+ "value\"with\"quotes"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "simple",
+ "plain_value"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_special_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(3, lines.size());
+ Collections.sort(lines);
+
+ // Values with commas should be quoted (sorted order: commas, quotes, simple)
+ Assert.assertEquals("commas,\"value,with,commas\"", lines.get(0));
+ // Values with quotes should be quoted and quotes doubled
+ Assert.assertEquals("quotes,\"value\"\"with\"\"quotes\"", lines.get(1));
+ // Simple values should not be quoted
+ Assert.assertEquals("simple,plain_value", lines.get(2));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithNullValues() throws IOException
+ {
+ final String tableName = "test_export_nulls";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB, description VARCHAR(255), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "with_values",
+ StringUtils.toUtf8("{\"key\":1}"),
+ "has_desc"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s (name) VALUES (?)", tableName),
+ "null_cols"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_nulls_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(2, lines.size());
+ Collections.sort(lines);
+
+ // Row with NULL payload and NULL description should have empty fields
+ Assert.assertEquals("null_cols,,", lines.get(0));
+
+ // Row with values
+ final String expectedHex = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"key\":1}"));
+ Assert.assertEquals("with_values," + expectedHex + ",has_desc", lines.get(1));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTablePreservesAllColumns() throws IOException
+ {
+ final String tableName = "test_export_allcols";
+ connector.getDBI().withHandle(
+ handle -> {
+ // Simulate segments table structure with columns after payload
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s ("
+ + "id VARCHAR(255) NOT NULL, "
+ + "used BOOLEAN NOT NULL, "
+ + "payload BLOB NOT NULL, "
+ + "used_status_last_updated VARCHAR(255), "
+ + "fingerprint VARCHAR(255), "
+ + "PRIMARY KEY(id))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?, ?, ?)", tableName),
+ "seg1",
+ true,
+ StringUtils.toUtf8("{\"v\":1}"),
+ "2024-01-01",
+ "fp_abc"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s (id, used, payload) VALUES (?, ?, ?)", tableName),
+ "seg2",
+ false,
+ StringUtils.toUtf8("{\"v\":2}")
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_allcols_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
Review Comment:
## CodeQL / Local information disclosure in a temporary directory
Local information disclosure vulnerability due to use of file readable by other local users.
[Show more details](https://github.com/apache/druid/security/code-scanning/11347)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3622008086
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,73 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath);
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ retryWithHandle(
+ (HandleCallback<Void>) handle -> {
+ try (Statement stmt = handle.getConnection().createStatement();
+ ResultSet rs = stmt.executeQuery(StringUtils.format("SELECT * FROM %s", tableName));
+ FileOutputStream fos = new FileOutputStream(outputPath);
+ OutputStreamWriter writer = new OutputStreamWriter(fos, StandardCharsets.UTF_8)) {
+ final ResultSetMetaData meta = rs.getMetaData();
+ final int columnCount = meta.getColumnCount();
+ while (rs.next()) {
+ for (int i = 1; i <= columnCount; i++) {
+ if (i > 1) {
+ writer.write(',');
+ }
+ final int colType = meta.getColumnType(i);
+ if (colType == Types.BINARY || colType == Types.VARBINARY
+ || colType == Types.LONGVARBINARY || colType == Types.BLOB
+ || (colType == Types.OTHER && "bytea".equalsIgnoreCase(meta.getColumnTypeName(i)))) {
+ final byte[] bytes = rs.getBytes(i);
+ if (bytes != null) {
+ writer.write(BaseEncoding.base16().encode(bytes));
+ }
+ } else if (colType == Types.BOOLEAN || colType == Types.BIT) {
+ final boolean val = rs.getBoolean(i);
+ if (!rs.wasNull()) {
+ writer.write(String.valueOf(val));
+ }
+ } else {
+ final String val = rs.getString(i);
+ if (val != null) {
+ if (val.contains(",") || val.contains("\"") || val.contains("\n") || val.contains("\r")) {
Review Comment:
The comma/quote round-trip is fixed, but multiline fields are still split because each rewrite uses `BufferedReader.readLine()` plus `CSVParser.parseLine()` on one physical line. `exportTableWithJdbc` writes quoted newline or carriage-return values as CSV records spanning lines, so the rewrite misparses them before `csvEscapeField` runs. Please use a record-aware `CSVReader`/`readNext` path and cover this end to end. Reviewed 8 of 8 changed files.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3607330467
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,73 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath);
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ retryWithHandle(
+ (HandleCallback<Void>) handle -> {
+ try (Statement stmt = handle.getConnection().createStatement();
Review Comment:
[P1] Stream PostgreSQL results instead of buffering the table
PostgreSQL JDBC buffers the complete `ResultSet` by default. This plain `Statement` runs on an auto-commit connection and never sets a fetch size; PostgreSQL cursor-based fetching requires `autoCommit=false` and a positive fetch size. Exporting a production segments table can therefore exhaust heap before rows are written. Run the query in a transaction and apply `getStreamingFetchSize()`.
##########
services/src/main/java/org/apache/druid/cli/ExportMetadata.java:
##########
@@ -245,12 +245,18 @@ private void exportTable(
} else {
pathFormatString = "%s/%s.csv";
}
+ final String exportTableName = isDerby() ? StringUtils.toUpperCase(tableName) : tableName;
dbConnector.exportTable(
- StringUtils.toUpperCase(tableName),
+ exportTableName,
Review Comment:
[P1] Preserve current segment columns in the importable CSV
The generic raw export preserves all columns, but `run()` immediately passes the file through `rewriteSegmentsExport`, which emits only columns 0 through 8. Current segment tables also contain required `used_status_last_updated` and additional fingerprint/upgrade columns. A PostgreSQL migration therefore loses those values, and the documented `COPY` into a freshly created current table fails because `used_status_last_updated` is `NOT NULL` without a default. The new test covers only the raw file; the final CSV and import commands need to preserve the current schema.
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,73 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath);
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ retryWithHandle(
+ (HandleCallback<Void>) handle -> {
+ try (Statement stmt = handle.getConnection().createStatement();
+ ResultSet rs = stmt.executeQuery(StringUtils.format("SELECT * FROM %s", tableName));
+ FileOutputStream fos = new FileOutputStream(outputPath);
+ OutputStreamWriter writer = new OutputStreamWriter(fos, StandardCharsets.UTF_8)) {
+ final ResultSetMetaData meta = rs.getMetaData();
+ final int columnCount = meta.getColumnCount();
+ while (rs.next()) {
+ for (int i = 1; i <= columnCount; i++) {
+ if (i > 1) {
+ writer.write(',');
+ }
+ final int colType = meta.getColumnType(i);
+ if (colType == Types.BINARY || colType == Types.VARBINARY
+ || colType == Types.LONGVARBINARY || colType == Types.BLOB
+ || (colType == Types.OTHER && "bytea".equalsIgnoreCase(meta.getColumnTypeName(i)))) {
+ final byte[] bytes = rs.getBytes(i);
+ if (bytes != null) {
+ writer.write(BaseEncoding.base16().encode(bytes));
+ }
+ } else if (colType == Types.BOOLEAN || colType == Types.BIT) {
+ final boolean val = rs.getBoolean(i);
+ if (!rs.wasNull()) {
+ writer.write(String.valueOf(val));
+ }
+ } else {
+ final String val = rs.getString(i);
+ if (val != null) {
+ if (val.contains(",") || val.contains("\"") || val.contains("\n") || val.contains("\r")) {
Review Comment:
[P2] Preserve CSV escaping through the rewrite stage
Although the raw writer correctly quotes commas and double quotes, each `ExportMetadata` rewrite parses those fields and concatenates them into the final CSV without re-escaping. Legal datasource and metadata identifiers may contain these characters, so the final importable file gains extra columns or malformed quoting even though the raw file is valid. Use a CSV writer or shared escaping routine in the rewrite stage and add an end-to-end test.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "JWuCines (via GitHub)" <gi...@apache.org>.
JWuCines commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3614617914
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,73 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath);
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ retryWithHandle(
+ (HandleCallback<Void>) handle -> {
+ try (Statement stmt = handle.getConnection().createStatement();
Review Comment:
Fixed in https://github.com/apache/druid/pull/19698/changes/6b73b7ee540578f9412a74d37c74f934f264b978
##########
services/src/main/java/org/apache/druid/cli/ExportMetadata.java:
##########
@@ -245,12 +245,18 @@ private void exportTable(
} else {
pathFormatString = "%s/%s.csv";
}
+ final String exportTableName = isDerby() ? StringUtils.toUpperCase(tableName) : tableName;
dbConnector.exportTable(
- StringUtils.toUpperCase(tableName),
+ exportTableName,
Review Comment:
Fixed in https://github.com/apache/druid/pull/19698/changes/6b73b7ee540578f9412a74d37c74f934f264b978
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,73 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath);
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ retryWithHandle(
+ (HandleCallback<Void>) handle -> {
+ try (Statement stmt = handle.getConnection().createStatement();
+ ResultSet rs = stmt.executeQuery(StringUtils.format("SELECT * FROM %s", tableName));
+ FileOutputStream fos = new FileOutputStream(outputPath);
+ OutputStreamWriter writer = new OutputStreamWriter(fos, StandardCharsets.UTF_8)) {
+ final ResultSetMetaData meta = rs.getMetaData();
+ final int columnCount = meta.getColumnCount();
+ while (rs.next()) {
+ for (int i = 1; i <= columnCount; i++) {
+ if (i > 1) {
+ writer.write(',');
+ }
+ final int colType = meta.getColumnType(i);
+ if (colType == Types.BINARY || colType == Types.VARBINARY
+ || colType == Types.LONGVARBINARY || colType == Types.BLOB
+ || (colType == Types.OTHER && "bytea".equalsIgnoreCase(meta.getColumnTypeName(i)))) {
+ final byte[] bytes = rs.getBytes(i);
+ if (bytes != null) {
+ writer.write(BaseEncoding.base16().encode(bytes));
+ }
+ } else if (colType == Types.BOOLEAN || colType == Types.BIT) {
+ final boolean val = rs.getBoolean(i);
+ if (!rs.wasNull()) {
+ writer.write(String.valueOf(val));
+ }
+ } else {
+ final String val = rs.getString(i);
+ if (val != null) {
+ if (val.contains(",") || val.contains("\"") || val.contains("\n") || val.contains("\r")) {
Review Comment:
Fixed in https://github.com/apache/druid/pull/19698/changes/6b73b7ee540578f9412a74d37c74f934f264b978
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 merged PR #19698:
URL: https://github.com/apache/druid/pull/19698
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3703838753
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,185 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema of the current connection, which is the schema an unqualified
+ * table name resolves to, and the table name is escaped so that it is matched literally rather than
+ * as a {@link DatabaseMetaData#getColumns} search pattern.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ final DatabaseMetaData dbMetaData = conn.getMetaData();
+ try (ResultSet rs = dbMetaData.getColumns(
+ null,
+ escapeMetaDataSearchString(dbMetaData, conn.getSchema()),
+ escapeMetaDataSearchString(dbMetaData, tableName),
Review Comment:
[P2] Normalize PostgreSQL identifiers before metadata lookup
PostgreSQL folds unquoted identifiers to lowercase, while DatabaseMetaData.getColumns uses a case-sensitive LIKE. With a valid mixed-case --base, tableExists succeeds via ILIKE and ordinary SQL resolves the lowercase table, but this lookup returns no columns, causing segment export to abort. Normalize according to JDBC identifier rules or resolve the actual table name first.
##########
docs/operations/export-metadata.md:
##########
@@ -151,14 +164,18 @@ In the example command above:
After running the tool, the output directory will contain `<table-name>_raw.csv` and `<table-name>.csv` files.
-The `<table-name>_raw.csv` files are intermediate files used by the tool, containing the table data as exported by Derby without modification.
+The `<table-name>_raw.csv` files are intermediate files used by the tool, containing the table data as exported from the source database without deep-storage rewrites. BLOB columns are hex-encoded and booleans are written as `true`/`false` strings.
The `<table-name>.csv` files are used for import into another database such as MySQL and PostgreSQL and have any configured deep storage location rewrites applied.
Example import commands for Derby, MySQL, and PostgreSQL are shown below.
These example import commands expect `/tmp/csv` and its contents to be accessible from the server. For other options, such as importing from the client filesystem, please refer to the database's documentation.
+The segments table is exported in a fixed column order, independent of the physical column order of the source table: `id`, `dataSource`, `created_date`, `start`, `end`, `partitioned`, `version`, `used`, `payload`, `used_status_last_updated`, `indexing_state_fingerprint`, `upgraded_from_segment_id`, followed by `schema_fingerprint` and `num_rows` if the source table has them. Add `schema_fingerprint,num_rows` to the end of the segments column list in the import commands below if those columns are present.
Review Comment:
[P1] Keep import lists aligned with older segment schemas
orderSegmentsColumns skips every absent column, and the new test explicitly supports legacy nine-column tables, but the documented PostgreSQL COPY always expects the three later columns. A nine-field export therefore fails with missing data. The instructions or generated output must identify every optional post-payload column and omit absent ones from the import list and FORCE_NULL.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "github-advanced-security[bot] (via GitHub)" <gi...@apache.org>.
github-advanced-security[bot] commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3614802376
##########
services/src/test/java/org/apache/druid/cli/ExportMetadataTest.java:
##########
@@ -0,0 +1,273 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.druid.cli;
+
+import com.google.common.io.BaseEncoding;
+import com.opencsv.CSVParser;
+import org.apache.druid.java.util.common.StringUtils;
+import org.junit.Assert;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.TemporaryFolder;
+
+import java.io.File;
+import java.io.FileOutputStream;
+import java.io.IOException;
+import java.io.OutputStreamWriter;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.util.List;
+
+public class ExportMetadataTest
+{
+ @Rule
+ public final TemporaryFolder tempFolder = new TemporaryFolder();
+
+ @Test
+ public void testCsvEscapeField_plainValue()
+ {
+ Assert.assertEquals("hello", ExportMetadata.csvEscapeField("hello"));
+ }
+
+ @Test
+ public void testCsvEscapeField_withComma()
+ {
+ Assert.assertEquals("\"value,with,commas\"", ExportMetadata.csvEscapeField("value,with,commas"));
+ }
+
+ @Test
+ public void testCsvEscapeField_withDoubleQuote()
+ {
+ Assert.assertEquals("\"value\"\"with\"\"quotes\"", ExportMetadata.csvEscapeField("value\"with\"quotes"));
+ }
+
+ @Test
+ public void testCsvEscapeField_withNewline()
+ {
+ Assert.assertEquals("\"line1\nline2\"", ExportMetadata.csvEscapeField("line1\nline2"));
+ }
+
+ @Test
+ public void testCsvEscapeField_withCarriageReturn()
+ {
+ Assert.assertEquals("\"line1\rline2\"", ExportMetadata.csvEscapeField("line1\rline2"));
+ }
+
+ @Test
+ public void testCsvEscapeField_null()
+ {
+ Assert.assertEquals("", ExportMetadata.csvEscapeField(null));
+ }
+
+ @Test
+ public void testCsvEscapeField_empty()
+ {
+ Assert.assertEquals("", ExportMetadata.csvEscapeField(""));
+ }
+
+ @Test
+ public void testRewriteSegmentsExport_preservesAllColumns() throws IOException
+ {
+ final File outputDir = tempFolder.newFolder("segments_export");
+ final String tableName = "druid_segments";
+
+ // Build a raw CSV with 12 columns matching the current segments table schema:
+ // id, dataSource, created_date, start, end, partitioned, version, used, payload,
+ // used_status_last_updated, indexing_state_fingerprint, upgraded_from_segment_id
+ final String payloadJson = "{\"type\":\"test\"}";
+ final String payloadHex = BaseEncoding.base16().encode(StringUtils.toUtf8(payloadJson));
+
+ final String rawLine = String.join(",",
+ "seg_id_1",
+ "my_datasource",
+ "2024-01-15",
+ "2024-01-01",
+ "2024-01-02",
+ "true",
+ "v1",
+ "true",
+ payloadHex,
+ "2024-06-01T00:00:00.000Z",
+ "fp_abc123",
+ "upgraded_seg_0"
+ );
+
+ final File rawFile = new File(outputDir, tableName + "_raw.csv");
+ try (OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(rawFile), StandardCharsets.UTF_8)) {
+ writer.write(rawLine + "\n");
+ }
+
+ final ExportMetadata exporter = new ExportMetadata();
+ exporter.outputPath = outputDir.getAbsolutePath();
+ exporter.useHexBlobs = false;
+ exporter.booleansAsStrings = false;
+
+ exporter.rewriteSegmentsExport(tableName);
+
+ final File outFile = new File(outputDir, tableName + ".csv");
+ Assert.assertTrue("Output CSV must exist", outFile.exists());
+
+ final List<String> lines = Files.readAllLines(outFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(1, lines.size());
+
+ // Parse the output with opencsv to verify field count and values
+ final CSVParser parser = new CSVParser();
+ final String[] fields = parser.parseLine(lines.get(0));
+
+ // Must have all 12 columns
+ Assert.assertEquals("All 12 columns must be preserved", 12, fields.length);
+
+ Assert.assertEquals("seg_id_1", fields[0]);
+ Assert.assertEquals("my_datasource", fields[1]);
+ Assert.assertEquals("2024-01-15", fields[2]);
+ Assert.assertEquals("2024-01-01", fields[3]);
+ Assert.assertEquals("2024-01-02", fields[4]);
+ Assert.assertEquals("1", fields[5]); // partitioned: true -> 1
+ Assert.assertEquals("v1", fields[6]);
+ Assert.assertEquals("1", fields[7]); // used: true -> 1
+
+ // payload should be escaped JSON, not hex
+ Assert.assertEquals(payloadJson, fields[8]);
+
+ // Additional columns preserved
+ Assert.assertEquals("2024-06-01T00:00:00.000Z", fields[9]);
+ Assert.assertEquals("fp_abc123", fields[10]);
+ Assert.assertEquals("upgraded_seg_0", fields[11]);
+ }
+
+ @Test
+ public void testRewriteSegmentsExport_withSpecialCharsInFields() throws IOException
+ {
+ final File outputDir = tempFolder.newFolder("segments_special");
+ final String tableName = "druid_segments";
+
+ final String payloadJson = "{\"type\":\"test\"}";
+ final String payloadHex = BaseEncoding.base16().encode(StringUtils.toUtf8(payloadJson));
+
+ // datasource with comma, version with quotes — these need proper CSV escaping
+ final String datasource = "ds,with,commas";
+ final String version = "v\"quoted\"";
+
+ // The raw CSV file from exportTableWithJdbc would have properly escaped these
+ final String rawLine = csvEscapeField(datasource) + ","
+ + csvEscapeField("seg,id,1") + "," // swapped: id first in raw but we're writing fields in schema order
+ + "2024-01-15,"
+ + "2024-01-01,"
+ + "2024-01-02,"
+ + "true,"
+ + csvEscapeField(version) + ","
+ + "false,"
+ + payloadHex + ","
+ + "2024-06-01";
Review Comment:
## CodeQL / Unread local variable
Variable 'String rawLine' is never read.
[Show more details](https://github.com/apache/druid/security/code-scanning/11350)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "JWuCines (via GitHub)" <gi...@apache.org>.
JWuCines commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3615036956
##########
services/src/test/java/org/apache/druid/cli/ExportMetadataTest.java:
##########
@@ -0,0 +1,273 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+package org.apache.druid.cli;
+
+import com.google.common.io.BaseEncoding;
+import com.opencsv.CSVParser;
+import org.apache.druid.java.util.common.StringUtils;
+import org.junit.Assert;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.TemporaryFolder;
+
+import java.io.File;
+import java.io.FileOutputStream;
+import java.io.IOException;
+import java.io.OutputStreamWriter;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.util.List;
+
+public class ExportMetadataTest
+{
+ @Rule
+ public final TemporaryFolder tempFolder = new TemporaryFolder();
+
+ @Test
+ public void testCsvEscapeField_plainValue()
+ {
+ Assert.assertEquals("hello", ExportMetadata.csvEscapeField("hello"));
+ }
+
+ @Test
+ public void testCsvEscapeField_withComma()
+ {
+ Assert.assertEquals("\"value,with,commas\"", ExportMetadata.csvEscapeField("value,with,commas"));
+ }
+
+ @Test
+ public void testCsvEscapeField_withDoubleQuote()
+ {
+ Assert.assertEquals("\"value\"\"with\"\"quotes\"", ExportMetadata.csvEscapeField("value\"with\"quotes"));
+ }
+
+ @Test
+ public void testCsvEscapeField_withNewline()
+ {
+ Assert.assertEquals("\"line1\nline2\"", ExportMetadata.csvEscapeField("line1\nline2"));
+ }
+
+ @Test
+ public void testCsvEscapeField_withCarriageReturn()
+ {
+ Assert.assertEquals("\"line1\rline2\"", ExportMetadata.csvEscapeField("line1\rline2"));
+ }
+
+ @Test
+ public void testCsvEscapeField_null()
+ {
+ Assert.assertEquals("", ExportMetadata.csvEscapeField(null));
+ }
+
+ @Test
+ public void testCsvEscapeField_empty()
+ {
+ Assert.assertEquals("", ExportMetadata.csvEscapeField(""));
+ }
+
+ @Test
+ public void testRewriteSegmentsExport_preservesAllColumns() throws IOException
+ {
+ final File outputDir = tempFolder.newFolder("segments_export");
+ final String tableName = "druid_segments";
+
+ // Build a raw CSV with 12 columns matching the current segments table schema:
+ // id, dataSource, created_date, start, end, partitioned, version, used, payload,
+ // used_status_last_updated, indexing_state_fingerprint, upgraded_from_segment_id
+ final String payloadJson = "{\"type\":\"test\"}";
+ final String payloadHex = BaseEncoding.base16().encode(StringUtils.toUtf8(payloadJson));
+
+ final String rawLine = String.join(",",
+ "seg_id_1",
+ "my_datasource",
+ "2024-01-15",
+ "2024-01-01",
+ "2024-01-02",
+ "true",
+ "v1",
+ "true",
+ payloadHex,
+ "2024-06-01T00:00:00.000Z",
+ "fp_abc123",
+ "upgraded_seg_0"
+ );
+
+ final File rawFile = new File(outputDir, tableName + "_raw.csv");
+ try (OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(rawFile), StandardCharsets.UTF_8)) {
+ writer.write(rawLine + "\n");
+ }
+
+ final ExportMetadata exporter = new ExportMetadata();
+ exporter.outputPath = outputDir.getAbsolutePath();
+ exporter.useHexBlobs = false;
+ exporter.booleansAsStrings = false;
+
+ exporter.rewriteSegmentsExport(tableName);
+
+ final File outFile = new File(outputDir, tableName + ".csv");
+ Assert.assertTrue("Output CSV must exist", outFile.exists());
+
+ final List<String> lines = Files.readAllLines(outFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(1, lines.size());
+
+ // Parse the output with opencsv to verify field count and values
+ final CSVParser parser = new CSVParser();
+ final String[] fields = parser.parseLine(lines.get(0));
+
+ // Must have all 12 columns
+ Assert.assertEquals("All 12 columns must be preserved", 12, fields.length);
+
+ Assert.assertEquals("seg_id_1", fields[0]);
+ Assert.assertEquals("my_datasource", fields[1]);
+ Assert.assertEquals("2024-01-15", fields[2]);
+ Assert.assertEquals("2024-01-01", fields[3]);
+ Assert.assertEquals("2024-01-02", fields[4]);
+ Assert.assertEquals("1", fields[5]); // partitioned: true -> 1
+ Assert.assertEquals("v1", fields[6]);
+ Assert.assertEquals("1", fields[7]); // used: true -> 1
+
+ // payload should be escaped JSON, not hex
+ Assert.assertEquals(payloadJson, fields[8]);
+
+ // Additional columns preserved
+ Assert.assertEquals("2024-06-01T00:00:00.000Z", fields[9]);
+ Assert.assertEquals("fp_abc123", fields[10]);
+ Assert.assertEquals("upgraded_seg_0", fields[11]);
+ }
+
+ @Test
+ public void testRewriteSegmentsExport_withSpecialCharsInFields() throws IOException
+ {
+ final File outputDir = tempFolder.newFolder("segments_special");
+ final String tableName = "druid_segments";
+
+ final String payloadJson = "{\"type\":\"test\"}";
+ final String payloadHex = BaseEncoding.base16().encode(StringUtils.toUtf8(payloadJson));
+
+ // datasource with comma, version with quotes — these need proper CSV escaping
+ final String datasource = "ds,with,commas";
+ final String version = "v\"quoted\"";
+
+ // The raw CSV file from exportTableWithJdbc would have properly escaped these
+ final String rawLine = csvEscapeField(datasource) + ","
+ + csvEscapeField("seg,id,1") + "," // swapped: id first in raw but we're writing fields in schema order
+ + "2024-01-15,"
+ + "2024-01-01,"
+ + "2024-01-02,"
+ + "true,"
+ + csvEscapeField(version) + ","
+ + "false,"
+ + payloadHex + ","
+ + "2024-06-01";
Review Comment:
Fixed on https://github.com/apache/druid/pull/19698/commits/4b4535409ad3fb274dbc465512bdf1ba2251ac4b
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3749656028
##########
docs/operations/export-metadata.md:
##########
@@ -151,46 +164,56 @@ In the example command above:
After running the tool, the output directory will contain `<table-name>_raw.csv` and `<table-name>.csv` files.
-The `<table-name>_raw.csv` files are intermediate files used by the tool, containing the table data as exported by Derby without modification.
+The `<table-name>_raw.csv` files are intermediate files used by the tool, containing the table data as exported from the source database without deep-storage rewrites. BLOB columns are hex-encoded and booleans are written as `true`/`false` strings.
The `<table-name>.csv` files are used for import into another database such as MySQL and PostgreSQL and have any configured deep storage location rewrites applied.
Example import commands for Derby, MySQL, and PostgreSQL are shown below.
These example import commands expect `/tmp/csv` and its contents to be accessible from the server. For other options, such as importing from the client filesystem, please refer to the database's documentation.
+The segments table is exported in a fixed column order, independent of the physical column order of the source table: `id`, `dataSource`, `created_date`, `start`, `end`, `partitioned`, `version`, `used`, `payload`, followed by whichever of the optional columns `used_status_last_updated`, `indexing_state_fingerprint`, `upgraded_from_segment_id`, `schema_fingerprint`, and `num_rows` exist in the source table, in that order. Adjust the segments column list in the import commands below to contain exactly the columns of the source table: omit any optional column the source table does not have (segments tables from older Druid versions may have only the first nine columns), and add `schema_fingerprint,num_rows` at the end if the source table has them. Apply the same adjustment to the columns declared with `FORCE_NULL` in the PostgreSQL command and to the user variables of the MySQL command.
+
+NULL values are written as empty fields, and each database needs to be told how to import them:
+
+- Derby imports an empty field as NULL, so no extra handling is needed.
+- PostgreSQL `COPY` imports an empty field as an empty string, which fails for non-string columns such as `num_rows`, so the command below declares the nullable columns with `FORCE_NULL`.
+- MySQL `LOAD DATA` imports an empty field as an empty string, and coerces it to `0` for numeric columns such as `num_rows`, so the command below reads the nullable columns into user variables and converts empty values to NULL with `NULLIF`.
+
+The exported CSV follows RFC 4180, in which a backslash is an ordinary character. MySQL `LOAD DATA` treats backslashes as escape characters by default, which would corrupt payloads and segment ids containing them, so the commands below disable this with `ESCAPED BY ''`.
+
### Derby
```sql
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'DRUID_SEGMENTS','/tmp/csv/druid_segments.csv',',','"',null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (null,'DRUID_SEGMENTS','id,dataSource,created_date,start,"end",partitioned,version,used,payload,used_status_last_updated,indexing_state_fingerprint,upgraded_from_segment_id',null,'/tmp/csv/druid_segments.csv',',','"',null,0);
Review Comment:
[P1] Legacy 9-column imports fail against current targets
Omitting used_status_last_updated from a legacy 9-column import leaves the current target's NOT NULL column unset, causing Derby/PostgreSQL imports to fail. Document a target-compatible default/value or make the migration create that column nullable before import.
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1054,6 +1061,181 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema returned by {@link #getMetadataTableSchema(Connection)}, which is the schema
+ * an unqualified table name resolves to. Rather than passing the table name as a search pattern, in which '_' is a
+ * wildcard and which is case-sensitive while the database folds unquoted identifiers, the returned table names are
+ * compared to the given one ignoring case.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ try (ResultSet rs = conn.getMetaData().getColumns(null, getMetadataTableSchema(conn), null, null)) {
+ while (rs.next()) {
+ if (tableName.equalsIgnoreCase(rs.getString("TABLE_NAME"))) {
+ columns.add(rs.getString("COLUMN_NAME"));
+ }
+ }
+ }
+ }
+ }
+ catch (SQLException e) {
+ log.warn(e, "Could not read columns of table[%s].", tableName);
+ }
+ return columns;
+ });
+ }
+
+ /**
+ * Returns the schema that the Druid metadata tables live in, i.e. the schema that an unqualified
+ * table name in a Druid SQL statement resolves to, or null if the schema is unknown and lookups
+ * should not be scoped to a schema.
+ *
+ * Connectors that scope {@link #tableExists} to a configured schema must override this so that
+ * both lookups agree.
+ */
+ @Nullable
+ protected String getMetadataTableSchema(final Connection connection) throws SQLException
+ {
+ return connection.getSchema();
+ }
+
+ /**
+ * Builds the select list for an export query, quoting each column with the database's
+ * identifier quote string so that reserved words such as "end" are handled correctly.
+ */
+ protected String makeExportSelectList(final Connection conn, final List<String> columns) throws SQLException
+ {
+ final String quote = conn.getMetaData().getIdentifierQuoteString();
+ if (quote == null || " ".equals(quote)) {
+ return String.join(",", columns);
+ }
+ return columns.stream()
+ .map(column -> quote + StringUtils.replace(column, quote, quote + quote) + quote)
+ .collect(Collectors.joining(","));
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ // Use a transaction so that the connection has autoCommit=false.
+ // PostgreSQL JDBC requires autoCommit=false and a positive fetch size
+ // to use cursor-based streaming instead of buffering the entire ResultSet.
+ retryTransaction(
+ (TransactionCallback<Void>) (handle, status) -> {
+ final Connection conn = handle.getConnection();
+ final String selectList = columns == null || columns.isEmpty() ? "*" : makeExportSelectList(conn, columns);
+ // Qualify the table with the schema that Druid's tables live in, which is not necessarily the schema an
+ // unqualified name resolves to for this connection. The identifiers are left unquoted so that they are
+ // folded by the database in the same way as in every other Druid statement.
+ final String schema = getMetadataTableSchema(conn);
+ final String qualifiedTableName = schema == null ? tableName : schema + "." + tableName;
+ try (Statement stmt = conn.createStatement()) {
+ final int fetchSize = getStreamingFetchSize();
+ if (fetchSize > 0) {
Review Comment:
[P2] MySQL streaming sentinel is discarded
MySQLConnector returns Integer.MIN_VALUE as its streaming sentinel, but the fetchSize > 0 guard discards it. Large exports then buffer results instead of streaming and can exhaust memory. Preserve connector-specific sentinel values or add a dedicated streaming hook.
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1054,6 +1061,181 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema returned by {@link #getMetadataTableSchema(Connection)}, which is the schema
+ * an unqualified table name resolves to. Rather than passing the table name as a search pattern, in which '_' is a
+ * wildcard and which is case-sensitive while the database folds unquoted identifiers, the returned table names are
+ * compared to the given one ignoring case.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ try (ResultSet rs = conn.getMetaData().getColumns(null, getMetadataTableSchema(conn), null, null)) {
+ while (rs.next()) {
+ if (tableName.equalsIgnoreCase(rs.getString("TABLE_NAME"))) {
+ columns.add(rs.getString("COLUMN_NAME"));
+ }
+ }
+ }
+ }
+ }
+ catch (SQLException e) {
+ log.warn(e, "Could not read columns of table[%s].", tableName);
+ }
+ return columns;
+ });
+ }
+
+ /**
+ * Returns the schema that the Druid metadata tables live in, i.e. the schema that an unqualified
+ * table name in a Druid SQL statement resolves to, or null if the schema is unknown and lookups
+ * should not be scoped to a schema.
+ *
+ * Connectors that scope {@link #tableExists} to a configured schema must override this so that
+ * both lookups agree.
+ */
+ @Nullable
+ protected String getMetadataTableSchema(final Connection connection) throws SQLException
+ {
+ return connection.getSchema();
+ }
+
+ /**
+ * Builds the select list for an export query, quoting each column with the database's
+ * identifier quote string so that reserved words such as "end" are handled correctly.
+ */
+ protected String makeExportSelectList(final Connection conn, final List<String> columns) throws SQLException
+ {
+ final String quote = conn.getMetaData().getIdentifierQuoteString();
+ if (quote == null || " ".equals(quote)) {
+ return String.join(",", columns);
+ }
+ return columns.stream()
+ .map(column -> quote + StringUtils.replace(column, quote, quote + quote) + quote)
+ .collect(Collectors.joining(","));
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ // Use a transaction so that the connection has autoCommit=false.
+ // PostgreSQL JDBC requires autoCommit=false and a positive fetch size
+ // to use cursor-based streaming instead of buffering the entire ResultSet.
+ retryTransaction(
+ (TransactionCallback<Void>) (handle, status) -> {
+ final Connection conn = handle.getConnection();
+ final String selectList = columns == null || columns.isEmpty() ? "*" : makeExportSelectList(conn, columns);
+ // Qualify the table with the schema that Druid's tables live in, which is not necessarily the schema an
+ // unqualified name resolves to for this connection. The identifiers are left unquoted so that they are
+ // folded by the database in the same way as in every other Druid statement.
+ final String schema = getMetadataTableSchema(conn);
+ final String qualifiedTableName = schema == null ? tableName : schema + "." + tableName;
Review Comment:
[P2] Configured PostgreSQL schema names are interpolated unquoted
A valid quoted schema such as MetaData is discovered correctly but is then queried as lowercase metadata, so export fails on case-sensitive PostgreSQL schemas. Quote the schema identifier using the JDBC driver's identifier-quote mechanism.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3698986553
##########
services/src/main/java/org/apache/druid/cli/ExportMetadata.java:
##########
@@ -386,20 +392,27 @@ private void rewriteSegmentsExport(
while ((line = reader.readLine()) != null) {
String[] parsed = PARSER.parseLine(line);
StringBuilder newLineBuilder = new StringBuilder();
- newLineBuilder.append(parsed[0]).append(","); //id
- newLineBuilder.append(parsed[1]).append(","); //dataSource
- newLineBuilder.append(parsed[2]).append(","); //created_date
- newLineBuilder.append(parsed[3]).append(","); //start
- newLineBuilder.append(parsed[4]).append(","); //end
+ newLineBuilder.append(csvEscapeField(parsed[0])).append(","); //id
+ newLineBuilder.append(csvEscapeField(parsed[1])).append(","); //dataSource
+ newLineBuilder.append(csvEscapeField(parsed[2])).append(","); //created_date
+ newLineBuilder.append(csvEscapeField(parsed[3])).append(","); //start
+ newLineBuilder.append(csvEscapeField(parsed[4])).append(","); //end
newLineBuilder.append(convertBooleanString(parsed[5])).append(","); //partitioned
- newLineBuilder.append(parsed[6]).append(","); //version
+ newLineBuilder.append(csvEscapeField(parsed[6])).append(","); //version
newLineBuilder.append(convertBooleanString(parsed[7])).append(","); //used
if (s3Bucket != null || hadoopStorageDirectory != null || newLocalPath != null) {
newLineBuilder.append(makePayloadWithConvertedLoadSpec(parsed[8]));
} else {
newLineBuilder.append(rewriteHexPayloadAsEscapedJson(parsed[8])); //payload
}
+
+ // Preserve any additional columns after payload (e.g. used_status_last_updated,
+ // indexing_state_fingerprint, upgraded_from_segment_id, schema_fingerprint, num_rows)
+ for (int i = 9; i < parsed.length; i++) {
Review Comment:
[P1] Update imports for preserved segment columns
Current segment tables emit at least 12 fields, and this loop now retains all of them, but the documented PostgreSQL and MySQL import commands still declare only the original nine columns. PostgreSQL `COPY` rejects every such row as extra data, while MySQL truncates or ignores trailing fields with warnings. Emit a deterministic schema-compatible order and update import commands to include all exported columns.
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,84 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath);
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ // Use a transaction so that the connection has autoCommit=false.
+ // PostgreSQL JDBC requires autoCommit=false and a positive fetch size
+ // to use cursor-based streaming instead of buffering the entire ResultSet.
+ retryTransaction(
+ (TransactionCallback<Void>) (handle, status) -> {
+ final Connection conn = handle.getConnection();
+ try (Statement stmt = conn.createStatement()) {
+ final int fetchSize = getStreamingFetchSize();
+ if (fetchSize > 0) {
+ stmt.setFetchSize(fetchSize);
+ }
+ try (ResultSet rs = stmt.executeQuery(StringUtils.format("SELECT * FROM %s", tableName));
+ FileOutputStream fos = new FileOutputStream(outputPath);
+ OutputStreamWriter writer = new OutputStreamWriter(fos, StandardCharsets.UTF_8)) {
+ final ResultSetMetaData meta = rs.getMetaData();
+ final int columnCount = meta.getColumnCount();
+ while (rs.next()) {
+ for (int i = 1; i <= columnCount; i++) {
+ if (i > 1) {
+ writer.write(',');
+ }
+ final int colType = meta.getColumnType(i);
+ if (colType == Types.BINARY || colType == Types.VARBINARY
+ || colType == Types.LONGVARBINARY || colType == Types.BLOB
+ || (colType == Types.OTHER && "bytea".equalsIgnoreCase(meta.getColumnTypeName(i)))) {
+ final byte[] bytes = rs.getBytes(i);
+ if (bytes != null) {
+ writer.write(BaseEncoding.base16().encode(bytes));
+ }
+ } else if (colType == Types.BOOLEAN || colType == Types.BIT) {
+ final boolean val = rs.getBoolean(i);
+ if (!rs.wasNull()) {
+ writer.write(String.valueOf(val));
+ }
+ } else {
+ final String val = rs.getString(i);
+ if (val != null) {
+ if (val.contains(",") || val.contains("\"") || val.contains("\n") || val.contains("\r")) {
Review Comment:
[P2] Preserve backslashes through CSV rewrites
The writer emits RFC 4180 data and leaves backslashes unchanged, but `ExportMetadata` reads it with default OpenCSV `CSVParser`, where backslash is the escape character. Consequently, a valid identifier such as `foo\bar` is parsed as `foobar` before `csvEscapeField` runs; Druid's ID validation explicitly permits backslashes. Use an RFC 4180 reader/parser or configure a null escape character, and add a round-trip test.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3880823861
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1055,6 +1062,194 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema returned by {@link #getMetadataTableSchema(Connection)}, which is the schema
+ * an unqualified table name resolves to. Rather than passing the table name as a search pattern, in which '_' is a
+ * wildcard and which is case-sensitive while the database folds unquoted identifiers, the returned table names are
+ * compared to the given one ignoring case.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ try (ResultSet rs = conn.getMetaData().getColumns(null, getMetadataTableSchema(conn), null, null)) {
Review Comment:
[P2] Schema lookup still treats the schema as a pattern
getColumns receives getMetadataTableSchema(conn) as its schema-pattern argument. JDBC metadata patterns treat '_' and '%' as wildcards, so a configured schema containing either character can return columns from other schemas; the later TABLE_NAME check does not verify TABLE_SCHEM. Escape the schema pattern or filter TABLE_SCHEM for an exact match.
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1055,6 +1062,194 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTable(tableName, outputPath, null);
+ }
+
+ /**
+ * Exports a table to a CSV file, emitting the given columns in the given order.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns in the
+ * order reported by the database
+ */
+ public void exportTable(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath, columns);
+ }
+
+ /**
+ * Returns the columns of the given table, in the order reported by the database.
+ * Returns an empty list if the table does not exist or the metadata cannot be read.
+ *
+ * The lookup is scoped to the schema returned by {@link #getMetadataTableSchema(Connection)}, which is the schema
+ * an unqualified table name resolves to. Rather than passing the table name as a search pattern, in which '_' is a
+ * wildcard and which is case-sensitive while the database folds unquoted identifiers, the returned table names are
+ * compared to the given one ignoring case.
+ */
+ public List<String> getTableColumns(final String tableName)
+ {
+ return getDBI().withHandle(handle -> {
+ final List<String> columns = new ArrayList<>();
+ try {
+ if (tableExists(handle, tableName)) {
+ final Connection conn = handle.getConnection();
+ try (ResultSet rs = conn.getMetaData().getColumns(null, getMetadataTableSchema(conn), null, null)) {
+ while (rs.next()) {
+ if (tableName.equalsIgnoreCase(rs.getString("TABLE_NAME"))) {
+ columns.add(rs.getString("COLUMN_NAME"));
+ }
+ }
+ }
+ }
+ }
+ catch (SQLException e) {
+ log.warn(e, "Could not read columns of table[%s].", tableName);
+ }
+ return columns;
+ });
+ }
+
+ /**
+ * Returns the schema that the Druid metadata tables live in, i.e. the schema that an unqualified
+ * table name in a Druid SQL statement resolves to, or null if the schema is unknown and lookups
+ * should not be scoped to a schema.
+ *
+ * Connectors that scope {@link #tableExists} to a configured schema must override this so that
+ * both lookups agree.
+ */
+ @Nullable
+ protected String getMetadataTableSchema(final Connection connection) throws SQLException
+ {
+ return connection.getSchema();
+ }
+
+ /**
+ * Builds the select list for an export query, quoting each column with the database's
+ * identifier quote string so that reserved words such as "end" are handled correctly.
+ */
+ protected String makeExportSelectList(final Connection conn, final List<String> columns) throws SQLException
+ {
+ final String quote = conn.getMetaData().getIdentifierQuoteString();
+ return columns.stream()
+ .map(column -> quoteIdentifier(quote, column))
+ .collect(Collectors.joining(","));
+ }
+
+ /**
+ * Quotes an identifier with the given identifier quote string of the database, doubling any occurrence of the quote
+ * string inside the identifier. Returns the identifier unchanged if the database does not support quoting, which
+ * {@link DatabaseMetaData#getIdentifierQuoteString()} reports as a space.
+ */
+ private static String quoteIdentifier(@Nullable final String quote, final String identifier)
+ {
+ if (quote == null || " ".equals(quote)) {
+ return identifier;
+ }
+ return quote + StringUtils.replace(identifier, quote, quote + quote) + quote;
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ *
+ * @param columns columns to export in the desired order, or null to export all columns
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath,
+ @Nullable final List<String> columns
+ )
+ {
+ // Use a transaction so that the connection has autoCommit=false.
+ // PostgreSQL JDBC requires autoCommit=false and a positive fetch size
+ // to use cursor-based streaming instead of buffering the entire ResultSet.
+ retryTransaction(
+ (TransactionCallback<Void>) (handle, status) -> {
+ final Connection conn = handle.getConnection();
+ final String selectList = columns == null || columns.isEmpty() ? "*" : makeExportSelectList(conn, columns);
+ // Qualify the table with the schema that Druid's tables live in, which is not necessarily the schema an
+ // unqualified name resolves to for this connection. The schema is quoted, since it is the name as stored in
+ // the database, while the table name is left unquoted so that it is folded by the database in the same way
+ // as in every other Druid statement.
+ final String schema = getMetadataTableSchema(conn);
+ final String qualifiedTableName =
+ schema == null
+ ? tableName
+ : quoteIdentifier(conn.getMetaData().getIdentifierQuoteString(), schema) + "." + tableName;
+ try (Statement stmt = conn.createStatement()) {
+ // Set the fetch size unconditionally: some drivers use a sentinel value to request streaming, such as
+ // Integer.MIN_VALUE in MySQL, which would be discarded by a positive-value check.
+ stmt.setFetchSize(getStreamingFetchSize());
+ try (ResultSet rs = stmt.executeQuery(
+ StringUtils.format("SELECT %s FROM %s", selectList, qualifiedTableName)
+ );
+ OutputStreamWriter writer =
+ new OutputStreamWriter(new FileOutputStream(outputPath), StandardCharsets.UTF_8)) {
+ final int columnCount = rs.getMetaData().getColumnCount();
+ final List<String> values = new ArrayList<>(columnCount);
+ while (rs.next()) {
+ values.clear();
+ for (int i = 1; i <= columnCount; i++) {
+ values.add(readCsvValue(rs, i));
+ }
+ writer.write(String.join(",", values));
+ writer.write('\n');
+ }
+ }
+ }
+ return null;
+ },
+ QUIET_RETRIES,
+ DEFAULT_MAX_TRIES
+ );
+ }
+
+ /**
+ * Reads the given column of the current row as a CSV field. Binary values are hex-encoded, booleans are written
+ * as true/false and NULLs as empty fields.
+ */
+ private static String readCsvValue(final ResultSet rs, final int column) throws SQLException
+ {
+ final ResultSetMetaData meta = rs.getMetaData();
+ final int type = meta.getColumnType(column);
+ if (type == Types.BINARY || type == Types.VARBINARY || type == Types.LONGVARBINARY || type == Types.BLOB
+ || (type == Types.OTHER && "bytea".equalsIgnoreCase(meta.getColumnTypeName(column)))) {
+ final byte[] bytes = rs.getBytes(column);
+ return bytes == null ? "" : BaseEncoding.base16().encode(bytes);
+ } else if (type == Types.BOOLEAN || type == Types.BIT) {
+ final boolean value = rs.getBoolean(column);
+ return rs.wasNull() ? "" : String.valueOf(value);
+ } else {
+ return csvEscape(rs.getString(column));
+ }
+ }
+
+ /**
+ * Escapes a value for CSV output as per RFC 4180: values containing a comma, double quote or line break are
+ * wrapped in double quotes, with inner double quotes doubled. A null value is written as an empty field.
+ */
+ public static String csvEscape(@Nullable final String value)
+ {
+ if (value == null) {
+ return "";
+ } else if (value.contains(",") || value.contains("\"") || value.contains("\n") || value.contains("\r")) {
Review Comment:
[P2] Empty strings are exported as NULL fields
csvEscape(null) and csvEscape("") both produce an unquoted empty field. PostgreSQL CSV interprets an unquoted empty field as an empty string, while the import guidance and FORCE_NULL columns rely on empty fields representing NULL; conversely, a real empty metadata value cannot round-trip distinctly from NULL. Quote empty strings or configure NULL handling explicitly so null and empty values remain distinct.
##########
docs/operations/export-metadata.md:
##########
@@ -151,46 +164,67 @@ In the example command above:
After running the tool, the output directory will contain `<table-name>_raw.csv` and `<table-name>.csv` files.
-The `<table-name>_raw.csv` files are intermediate files used by the tool, containing the table data as exported by Derby without modification.
+The `<table-name>_raw.csv` files are intermediate files used by the tool, containing the table data as exported from the source database without deep-storage rewrites. BLOB columns are hex-encoded and booleans are written as `true`/`false` strings.
The `<table-name>.csv` files are used for import into another database such as MySQL and PostgreSQL and have any configured deep storage location rewrites applied.
Example import commands for Derby, MySQL, and PostgreSQL are shown below.
These example import commands expect `/tmp/csv` and its contents to be accessible from the server. For other options, such as importing from the client filesystem, please refer to the database's documentation.
+The segments table is exported in a fixed column order, independent of the physical column order of the source table: `id`, `dataSource`, `created_date`, `start`, `end`, `partitioned`, `version`, `used`, `payload`, followed by whichever of the optional columns `used_status_last_updated`, `indexing_state_fingerprint`, `upgraded_from_segment_id`, `schema_fingerprint`, and `num_rows` exist in the source table, in that order. Adjust the segments column list in the import commands below to contain exactly the columns of the source table: omit any optional column the source table does not have (segments tables from older Druid versions may have only the first nine columns), and add `schema_fingerprint,num_rows` at the end if the source table has them. Apply the same adjustment to the columns declared with `FORCE_NULL` in the PostgreSQL command and to the user variables of the MySQL command.
+
+If the source table does not have `used_status_last_updated` but the target table does, the import fails, because Druid creates that column as `NOT NULL` without a default. Make the column nullable before importing, and give the imported rows a value afterwards:
+
+```sql
+ALTER TABLE druid_segments ALTER COLUMN used_status_last_updated NULL;
+-- run the import command for your database, omitting used_status_last_updated from the column list
+UPDATE druid_segments SET used_status_last_updated = created_date WHERE used_status_last_updated IS NULL;
+ALTER TABLE druid_segments ALTER COLUMN used_status_last_updated NOT NULL;
+```
+
+The `ALTER TABLE` syntax above is for Derby. On PostgreSQL, use `ALTER COLUMN used_status_last_updated DROP NOT NULL` and `SET NOT NULL`; on MySQL, use `MODIFY used_status_last_updated VARCHAR(255) NULL` and `MODIFY used_status_last_updated VARCHAR(255) NOT NULL`.
+
+NULL values are written as empty fields, and each database needs to be told how to import them:
+
+- Derby imports an empty field as NULL, so no extra handling is needed.
+- PostgreSQL `COPY` imports an empty field as an empty string, which fails for non-string columns such as `num_rows`, so the command below declares the nullable columns with `FORCE_NULL`.
+- MySQL `LOAD DATA` imports an empty field as an empty string, and coerces it to `0` for numeric columns such as `num_rows`, so the command below reads the nullable columns into user variables and converts empty values to NULL with `NULLIF`.
+
+The exported CSV follows RFC 4180, in which a backslash is an ordinary character. MySQL `LOAD DATA` treats backslashes as escape characters by default, which would corrupt payloads and segment ids containing them, so the commands below disable this with `ESCAPED BY ''`.
+
### Derby
```sql
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'DRUID_SEGMENTS','/tmp/csv/druid_segments.csv',',','"',null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (null,'DRUID_SEGMENTS','id,dataSource,created_date,start,"end",partitioned,version,used,payload,used_status_last_updated,indexing_state_fingerprint,upgraded_from_segment_id',null,'/tmp/csv/druid_segments.csv',',','"',null,0);
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'DRUID_RULES','/tmp/csv/druid_rules.csv',',','"',null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (null,'DRUID_RULES','id,dataSource,version,payload',null,'/tmp/csv/druid_rules.csv',',','"',null,0);
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'DRUID_CONFIG','/tmp/csv/druid_config.csv',',','"',null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (null,'DRUID_CONFIG','name,payload',null,'/tmp/csv/druid_config.csv',',','"',null,0);
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'DRUID_DATASOURCE','/tmp/csv/druid_dataSource.csv',',','"',null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (null,'DRUID_DATASOURCE','dataSource,created_date,commit_metadata_payload,commit_metadata_sha1',null,'/tmp/csv/druid_dataSource.csv',',','"',null,0);
-CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE (null,'DRUID_SUPERVISORS','/tmp/csv/druid_supervisors.csv',',','"',null,0);
+CALL SYSCS_UTIL.SYSCS_IMPORT_DATA (null,'DRUID_SUPERVISORS','id,spec_id,created_date,payload',null,'/tmp/csv/druid_supervisors.csv',',','"',null,0);
```
### MySQL
```sql
-LOAD DATA INFILE '/tmp/csv/druid_segments.csv' INTO TABLE druid_segments FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' (id,dataSource,created_date,start,end,partitioned,version,used,payload); SHOW WARNINGS;
+LOAD DATA INFILE '/tmp/csv/druid_segments.csv' INTO TABLE druid_segments FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' (id,dataSource,created_date,start,end,partitioned,version,used,payload,@used_status_last_updated,@indexing_state_fingerprint,@upgraded_from_segment_id) SET used_status_last_updated=NULLIF(@used_status_last_updated,''), indexing_state_fingerprint=NULLIF(@indexing_state_fingerprint,''), upgraded_from_segment_id=NULLIF(@upgraded_from_segment_id,''); SHOW WARNINGS;
-LOAD DATA INFILE '/tmp/csv/druid_rules.csv' INTO TABLE druid_rules FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' (id,dataSource,version,payload); SHOW WARNINGS;
+LOAD DATA INFILE '/tmp/csv/druid_rules.csv' INTO TABLE druid_rules FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' (id,dataSource,version,payload); SHOW WARNINGS;
-LOAD DATA INFILE '/tmp/csv/druid_config.csv' INTO TABLE druid_config FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' (name,payload); SHOW WARNINGS;
+LOAD DATA INFILE '/tmp/csv/druid_config.csv' INTO TABLE druid_config FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' (name,payload); SHOW WARNINGS;
-LOAD DATA INFILE '/tmp/csv/druid_dataSource.csv' INTO TABLE druid_dataSource FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' (dataSource,created_date,commit_metadata_payload,commit_metadata_sha1); SHOW WARNINGS;
+LOAD DATA INFILE '/tmp/csv/druid_dataSource.csv' INTO TABLE druid_dataSource FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' (dataSource,created_date,commit_metadata_payload,commit_metadata_sha1); SHOW WARNINGS;
-LOAD DATA INFILE '/tmp/csv/druid_supervisors.csv' INTO TABLE druid_supervisors FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' (id,spec_id,created_date,payload); SHOW WARNINGS;
+LOAD DATA INFILE '/tmp/csv/druid_supervisors.csv' INTO TABLE druid_supervisors FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '' (id,spec_id,created_date,payload); SHOW WARNINGS;
```
### PostgreSQL
```sql
-COPY druid_segments(id,dataSource,created_date,start,"end",partitioned,version,used,payload) FROM '/tmp/csv/druid_segments.csv' DELIMITER ',' CSV;
+COPY druid_segments(id,dataSource,created_date,start,"end",partitioned,version,used,payload,used_status_last_updated,indexing_state_fingerprint,upgraded_from_segment_id) FROM '/tmp/csv/druid_segments.csv' WITH (FORMAT csv, FORCE_NULL (used_status_last_updated,indexing_state_fingerprint,upgraded_from_segment_id));
Review Comment:
[P1] PostgreSQL COPY can corrupt rewritten BYTEA payloads
The PostgreSQL import example uses COPY CSV with backslash escapes enabled, while the exporter writes BYTEA values as JSON text containing literal backslashes. PostgreSQL can interpret those backslashes as escape syntax, causing escaped payloads to fail or change during import. Emit BYTEA as \x-prefixed hex or use an explicit decode expression so the documented import round-trips the exported data.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "JWuCines (via GitHub)" <gi...@apache.org>.
JWuCines commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3602926640
##########
server/src/test/java/org/apache/druid/metadata/SQLMetadataConnectorTest.java:
##########
@@ -463,6 +468,219 @@
);
}
+ @Test
+ public void testExportTable() throws IOException
+ {
+ final String tableName = "test_export";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB NOT NULL, active BOOLEAN NOT NULL, PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key1",
+ StringUtils.toUtf8("{\"type\":\"test\"}"),
+ true
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key2",
+ StringUtils.toUtf8("{\"value\":42}"),
+ false
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_test", ".csv");
+ outputFile.deleteOnExit();
+
+ // Call the base class exportTable (the generic JDBC path used by PostgreSQL)
+ // rather than DerbyConnector's native SYSCS_EXPORT_TABLE override
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(2, lines.size());
+ Collections.sort(lines);
+
+ // Verify rows (sorted by name): hex-encoded payload, boolean as string
+ final String expectedHex1 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"type\":\"test\"}"));
+ Assert.assertEquals("key1," + expectedHex1 + ",true", lines.get(0));
+
+ final String expectedHex2 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"value\":42}"));
+ Assert.assertEquals("key2," + expectedHex2 + ",false", lines.get(1));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithSpecialCharacters() throws IOException
+ {
+ final String tableName = "test_export_special";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, description VARCHAR(1024), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "commas",
+ "value,with,commas"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "quotes",
+ "value\"with\"quotes"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "simple",
+ "plain_value"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_special_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(3, lines.size());
+ Collections.sort(lines);
+
+ // Values with commas should be quoted (sorted order: commas, quotes, simple)
+ Assert.assertEquals("commas,\"value,with,commas\"", lines.get(0));
+ // Values with quotes should be quoted and quotes doubled
+ Assert.assertEquals("quotes,\"value\"\"with\"\"quotes\"", lines.get(1));
+ // Simple values should not be quoted
+ Assert.assertEquals("simple,plain_value", lines.get(2));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithNullValues() throws IOException
+ {
+ final String tableName = "test_export_nulls";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB, description VARCHAR(255), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "with_values",
+ StringUtils.toUtf8("{\"key\":1}"),
+ "has_desc"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s (name) VALUES (?)", tableName),
+ "null_cols"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_nulls_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
Review Comment:
[Use Files.createTempFile instead of File.createTempFile for owner-only permissions](https://github.com/apache/druid/pull/19698/changes/c2d66e690e011db4ed9c3017c5b4cabd950779f4)
##########
server/src/test/java/org/apache/druid/metadata/SQLMetadataConnectorTest.java:
##########
@@ -463,6 +468,219 @@
);
}
+ @Test
+ public void testExportTable() throws IOException
+ {
+ final String tableName = "test_export";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB NOT NULL, active BOOLEAN NOT NULL, PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key1",
+ StringUtils.toUtf8("{\"type\":\"test\"}"),
+ true
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "key2",
+ StringUtils.toUtf8("{\"value\":42}"),
+ false
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_test", ".csv");
+ outputFile.deleteOnExit();
+
+ // Call the base class exportTable (the generic JDBC path used by PostgreSQL)
+ // rather than DerbyConnector's native SYSCS_EXPORT_TABLE override
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(2, lines.size());
+ Collections.sort(lines);
+
+ // Verify rows (sorted by name): hex-encoded payload, boolean as string
+ final String expectedHex1 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"type\":\"test\"}"));
+ Assert.assertEquals("key1," + expectedHex1 + ",true", lines.get(0));
+
+ final String expectedHex2 = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"value\":42}"));
+ Assert.assertEquals("key2," + expectedHex2 + ",false", lines.get(1));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithSpecialCharacters() throws IOException
+ {
+ final String tableName = "test_export_special";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, description VARCHAR(1024), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "commas",
+ "value,with,commas"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "quotes",
+ "value\"with\"quotes"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?)", tableName),
+ "simple",
+ "plain_value"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_special_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(3, lines.size());
+ Collections.sort(lines);
+
+ // Values with commas should be quoted (sorted order: commas, quotes, simple)
+ Assert.assertEquals("commas,\"value,with,commas\"", lines.get(0));
+ // Values with quotes should be quoted and quotes doubled
+ Assert.assertEquals("quotes,\"value\"\"with\"\"quotes\"", lines.get(1));
+ // Simple values should not be quoted
+ Assert.assertEquals("simple,plain_value", lines.get(2));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTableWithNullValues() throws IOException
+ {
+ final String tableName = "test_export_nulls";
+ connector.getDBI().withHandle(
+ handle -> {
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s (name VARCHAR(255) NOT NULL, payload BLOB, description VARCHAR(255), PRIMARY KEY(name))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?)", tableName),
+ "with_values",
+ StringUtils.toUtf8("{\"key\":1}"),
+ "has_desc"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s (name) VALUES (?)", tableName),
+ "null_cols"
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_nulls_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
+
+ final List<String> lines = Files.readAllLines(outputFile.toPath(), StandardCharsets.UTF_8);
+ Assert.assertEquals(2, lines.size());
+ Collections.sort(lines);
+
+ // Row with NULL payload and NULL description should have empty fields
+ Assert.assertEquals("null_cols,,", lines.get(0));
+
+ // Row with values
+ final String expectedHex = BaseEncoding.base16().encode(StringUtils.toUtf8("{\"key\":1}"));
+ Assert.assertEquals("with_values," + expectedHex + ",has_desc", lines.get(1));
+
+ dropTable(tableName);
+ }
+
+ @Test
+ public void testExportTablePreservesAllColumns() throws IOException
+ {
+ final String tableName = "test_export_allcols";
+ connector.getDBI().withHandle(
+ handle -> {
+ // Simulate segments table structure with columns after payload
+ handle.execute(
+ StringUtils.format(
+ "CREATE TABLE %s ("
+ + "id VARCHAR(255) NOT NULL, "
+ + "used BOOLEAN NOT NULL, "
+ + "payload BLOB NOT NULL, "
+ + "used_status_last_updated VARCHAR(255), "
+ + "fingerprint VARCHAR(255), "
+ + "PRIMARY KEY(id))",
+ tableName
+ )
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s VALUES (?, ?, ?, ?, ?)", tableName),
+ "seg1",
+ true,
+ StringUtils.toUtf8("{\"v\":1}"),
+ "2024-01-01",
+ "fp_abc"
+ );
+ handle.execute(
+ StringUtils.format("INSERT INTO %s (id, used, payload) VALUES (?, ?, ?)", tableName),
+ "seg2",
+ false,
+ StringUtils.toUtf8("{\"v\":2}")
+ );
+ return null;
+ }
+ );
+
+ final File outputFile = File.createTempFile("export_allcols_test", ".csv");
+ outputFile.deleteOnExit();
+
+ connector.exportTableGeneric(
+ StringUtils.toUpperCase(tableName),
+ outputFile.getAbsolutePath()
+ );
Review Comment:
[Use Files.createTempFile instead of File.createTempFile for owner-only permissions](https://github.com/apache/druid/pull/19698/changes/c2d66e690e011db4ed9c3017c5b4cabd950779f4)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org
Re: [PR] feat: Support PostgreSQL export for export-metadata (druid)
Posted by "FrankChen021 (via GitHub)" <gi...@apache.org>.
FrankChen021 commented on code in PR #19698:
URL: https://github.com/apache/druid/pull/19698#discussion_r3622006007
##########
server/src/main/java/org/apache/druid/metadata/SQLMetadataConnector.java:
##########
@@ -1038,6 +1045,73 @@ public void createAuditTable()
}
}
+ @Override
+ public void exportTable(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ exportTableWithJdbc(tableName, outputPath);
+ }
+
+ /**
+ * Exports a table to a CSV file using generic JDBC.
+ * Binary columns are hex-encoded and booleans are written as true/false strings.
+ * Subclasses may override {@link #exportTable} with a database-specific implementation
+ * while this method remains available for testing or fallback.
+ */
+ protected void exportTableWithJdbc(
+ final String tableName,
+ final String outputPath
+ )
+ {
+ retryWithHandle(
+ (HandleCallback<Void>) handle -> {
+ try (Statement stmt = handle.getConnection().createStatement();
Review Comment:
Confirmed: `retryTransaction` gives PostgreSQL an auto-commit-disabled connection, and `getStreamingFetchSize()` is applied before query execution, enabling cursor-based fetching. Reviewed 8 of 8 changed files.
##########
services/src/main/java/org/apache/druid/cli/ExportMetadata.java:
##########
@@ -245,12 +245,18 @@ private void exportTable(
} else {
pathFormatString = "%s/%s.csv";
}
+ final String exportTableName = isDerby() ? StringUtils.toUpperCase(tableName) : tableName;
dbConnector.exportTable(
- StringUtils.toUpperCase(tableName),
+ exportTableName,
Review Comment:
Thanks; the rewrite now preserves trailing values, but the documented PostgreSQL `COPY druid_segments(...)` command still names only the original nine columns. Current exports contain 12–14 fields, so `COPY` fails with extra data; upgraded source tables can also have added columns in a different physical order. Please export segments in an explicit stable column order and update the import column lists accordingly. Reviewed 8 of 8 changed files.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org