PostgreSQL Metadata
The PostgreSQL Metadata processor determines the PostgreSQL table where each record should be written, compares the record structure against the table structure, then creates or alters the table as needed. For information about supported versions, see Supported Systems and Versions in the Data Collector documentation.
Use the PostgreSQL Metadata processor as part of the Drift Synchronization Solution for PostgreSQL.
When processing data, the PostgreSQL Metadata processor uses a table name expression to determine the name of the target table to use for each record. If the target table is not in the processor's cache, the processor queries the database for table information and caches the results. When the target table is in the cache, the processor compares the record structure against cached table structure.
When a record includes fields that do not exist in the table, the PostgreSQL Metadata processor alters the table as needed, then updates the table information in the cache. When a record should be written to a table that does not exist, the processor creates the table based on the fields in the record.
Like other database-related stages, when you configure the PostgreSQL Metadata processor, you can specify custom JDBC properties, enter connection credentials, and configure advanced properties such as an initial query and timeouts.
For more information about the Drift Synchronization Solution for PostgreSQL and a case study, see Drift Synchronization Solution for PostgreSQL.
JDBC Driver
When connecting to a PostgreSQL database, you do not need to install a JDBC driver. Data Collector includes the JDBC driver required for PostgreSQL.
Schema and Table Names
When you configure the schemas and tables where records should be written, you can use the actual schema and table names or expressions that resolve to the schemas and tables to use.
Use names when all data can be written to the same schema or table. Use expressions to use information in the record to determine the schema or table to write to.
For example, the JDBC Multitable Consumer origin writes the
originating table name in the jdbc.tables
record header
attribute.
If you want to write records to tables of the
same name, you can use ${record:attribute('jdbc.tables')}
for the table name property.
Similarly, the JDBC Query Consumer writes the originating table name
in a <user-defined prefix>.tables
record header
attribute when the origin is configured to create record header
attributes.
So if you want to write records to tables of the
same name, you can use ${record:attribute('<user defined
prefix>.tables')}
for the table name property.
-
If all records are to be written to a single schema or table, you can enter the schema or table name instead of an expression.
-
If the schema or table name can be extrapolated from record data or header attributes, you can use an expression that evaluates to the schema or table name.
- When necessary, you can use an Expression Evaluator earlier in the pipeline to perform calculations and write the results to a new field or a header attribute. Then, configure the PostgreSQL Metadata processor to use that information.
Decimal Precision and Scale Field Attributes
Use the Decimal Precision Attribute and Decimal Scale Attribute properties to specify the precision and scale for the Decimal columns that the PostgreSQL Metadata processor creates.
While other data types have hard coded definitions that the processor uses to create columns in database tables, Decimal columns require a specified precision and scale.
When processing data from the JDBC Query Consumer or JDBC Multitable Consumer origins, use the default attribute names, "precision" and "scale". Both origins store the precision and scale of Decimal columns in "precision" and "scale" field attributes for each Decimal field.
When processing data from other origins, you can use the Expression Evaluator processor earlier in the pipeline to create precision and scale field attributes for Decimal fields.
Caching Information
When processing records, the PostgreSQL Metadata processor queries the database for the necessary table information and caches the results. After creating or altering a table, it updates the table information in the cache. The processor uses the cache for record comparison when possible, to avoid unnecessary queries.
PostgreSQL Data Types
Data Collector Data Type | PostgreSQL Data Type |
---|---|
Boolean | Boolean |
Byte Array | Bytea |
Char | Character |
Date | Date |
Datetime | Timestamp without time zone |
Decimal | Numeric |
Double | Double precision |
Float | Real |
Integer | Integer |
Long | Bigint |
List | Not supported |
List-Map | Not supported |
Map | Not supported |
Short | Smallint |
String | Character varying |
Time | Time without time zone |
Zoned Datetime | Timestamp with time zone |
Configuring a PostgreSQL Metadata Processor
Configure a PostgreSQL Metadata processor as part of the Drift Synchronization Solution for PostgreSQL.