# @dawadk/import-util > This package contains utilities for importing data into the DAWA database. Latest version **6.2.0** (published 2023-12-01) · MIT license · 0 weekly downloads ## Install ```sh npm install @dawadk/import-util pnpm add @dawadk/import-util yarn add @dawadk/import-util bun add @dawadk/import-util ``` ## Health **Score 15/100 (F)** — status: abandoned. Positive: no vulnerabilities. Warnings: low downloads; no types; no esm support. Negative: abandoned; low maintenance score. ## Facts | | | |---|---| | Version | 6.2.0 | | Published | 2023-12-01 | | First published | 2018-12-11 | | Weekly downloads | 0 | | License | MIT | | TypeScript types | none | | Module format | CommonJS | | Dependencies | 18 | | Unpacked size | 108.2 KB | | Known vulnerabilities | 0 (+10 in 4 direct dependencies) | | Install scripts | no | | Author | Styrelsen for Dataforsyning og Infrastruktur | | Maintainers | iamfrank, dataforsyningen-user | | Keywords | dawa, import utilities, SDFI | ## Links - npm: https://www.npmjs.com/package/@dawadk/import-util - Repository: https://github.com/SDFIdk/DAWA - Homepage: https://github.com/SDFIdk/DAWA/tree/master/packages/import-util#readme - Issues: https://github.com/SDFIdk/DAWA/issues - npm.io page: https://npm.io/package/@dawadk/import-util ## Dependencies (18) - [q](https://npm.io/package/q.md) ^1.5.1 - [tmp](https://npm.io/package/tmp.md) ^0.1.0 - [chai](https://npm.io/package/chai.md) ^4.2.0 - [s3rver](https://npm.io/package/s3rver.md) ^2.2.8 - [split2](https://npm.io/package/split2.md) ^3.0.0 - [ts-csp](https://npm.io/package/ts-csp.md) ^0.9.0 - [aws-sdk](https://npm.io/package/aws-sdk.md) 2.461.0 - [convict](https://npm.io/package/convict.md) ^5.0.0 - [bluebird](https://npm.io/package/bluebird.md) ^3.5.4 - [through2](https://npm.io/package/through2.md) ^3.0.0 - [csv-parse](https://npm.io/package/csv-parse.md) ^4.4.1 - [lodash.get](https://npm.io/package/lodash.get.md) ^4.4.2 - [underscore](https://npm.io/package/underscore.md) ^1.9.1 - [event-stream](https://npm.io/package/event-stream.md) ^4.0.1 - [csv-stringify](https://npm.io/package/csv-stringify.md) ^5.3.0 - [@dawadk/common](https://npm.io/package/@dawadk/common.md) ^4.0.0 - [@mapbox/polylabel](https://npm.io/package/@mapbox/polylabel.md) ^1.0.2 - [object-assign-deep](https://npm.io/package/object-assign-deep.md) ^0.4.0 ## Recent versions - 6.2.0 (latest) — 2023-12-01 - 6.1.0 — 2023-10-31 - 6.0.0 — 2021-03-31 - 3.0.1 — 2019-07-30 - 3.0.0 — 2019-06-26 - 2.0.13 — 2019-05-28 - 2.0.7 — 2019-05-02 - 2.0.6 — 2019-05-02 - 2.0.5 — 2019-05-02 - 1.0.15 — 2019-03-08 - 1.0.13 — 2019-01-17 - 1.0.12 — 2019-01-10 - 1.0.11 — 2018-12-13 - 1.0.10 — 2018-12-12 - 1.0.9 — 2018-12-11 - … 1 more at https://npm.io/package/@dawadk/import-util/versions ## README # DAWA Import utilities This package contains utilities for importing data into the DAWA database. ## Overview DAWA is based on the PostgreSQL database. All importing logic is essentially SQL based. Data from the different data sources is streamed to the database, and is then operated on using SQL statements. Thus, this library is primarily a library for streaming data to the database and generating SQL statements to operate on these data. The DAWA database is large. It consists of more than a hundred tables, originating from around 10 different sources. Furthermore, the database is highly denormalized in order to provide efficient queries and maintain API compatibility. An additional challenge is that all data is provided to consumers via the replication API. Therefore, events must be generated and stored reliably whenever the data is changed. For (almost) every table in DAWA, an associated *change table* stores all changes to table. We may distinguish between *base tables*, which contain normalized data in a format similar to the data provided by the data sources, and *derived tables*, which combines data from multiple base tables as well as additional columns such as PostgreSQL term search vectors for full text searching. Derived tables are usually specified using *SQL views*. ## Importing process Data importing follows the following pattern: * Data from the source is streamed directly into a temporary table in the database. * The temporary table containing the *desired state* is compared to the *current state* in the base table. The operations (insert, update, delete) required to reconciliate are stored in the associated change table. * The operations are *applied* to the base table. Thereby, the current state becomes the desired state. * For every derived table, the *current state* in the derived table is compared with the *desired state* expressed by the SQL view associated with the derived table. Once again, the changes are stored in the associated change table. Some importers are capable of running *incrementally* without recomputing the entire state of the table. ## Transactions and events All data manipulation is transactional. For each transaction, a unique *transaction ID* (`txid`) is generated. Transactions are stored in the `transactions` table. Each event describes a *single* operation on a table, either an `insert`, `update` or `delete` operation. Events, changes and operations are generally used interchangeably. Every changes is associated with a transaction. The table `tx_operation_counts` stores the number of changes performed in each transaction. ## Change tables Events are stored in change tables. There is one change table for each primary table. It is conventionally named by the name of the primary table suffixed by "_changes". It has the same columns as the primary table and the following additional columns: * `txid`: The transaction in which the change was performed. * `operation`: Whether the change is an `insert`, `update` or `delete`. * `public`: A boolean indicating whether the change is public (visible on the Replication API) or private (not visible - only internal columns such as TSVs are modified). The concept of non-public events is deprecated. Instead, it is preferred to create additional derived tables with any non-public columns. * `changeid`: The sequence number (sekvensnummer) for the event. Sequence numbers are deprecated, and generated for backwards compatibility purposes only. ## Streaming to database The PostgreSQL database is able to receive streaming data in CSV format directly into a table using the [COPY](https://www.postgresql.org/docs/current/sql-copy.html) command. We use NodeJS streams to stream directly from input files to database. The file `src/postgres-streaming.js` contains utility functions around the COPY command. ## Comparing tables Comparing tables is an essential part of the importing process. The implementation of this functionality is in the file `src/table-diff.js`. The comparison process is also capable of deriving new data, such as [TSVs](https://www.postgresql.org/docs/current/textsearch.html), bounding boxes, visual centers and timestamps. The comparison function requires a description of the table structure in order to perform comparisons with a source table or view. This description is called a *table model*. The table model specifies: * The *name* of the table. * The *columns* of the table and how they behave in the comparison process. * The *primary key* column(s) of the table. Each column of a table model must implement the protocol specified in `src/table-diff-protocol.js` - see the source code docs for details. Most columns simply compare value of the table with the value from the source, but other columns implement complex behaviors, such as uploading their content to S3. Changing data in tables simply involves applying the operations in the change table to the primary table. ## Comparing changes incrementally In addition to comparing an entire table agains a source table, it is also possible to perform such a comparison on a *subset* of the table. Before this can happen, a list of *dirty* rows must be computed, that is, rows that may potentially be changed. The primary keys of the dirty rows are stored in a temporary *dirty table*. ## Generating sequence numbers Sequence numbers are generated at the *end* of the importing transaction. The code generating the sequence numbers can be found in the file `src/transaction.js`. ## Derived tables (materializations) Derived tables are also called *materializations* beacuse they resemble SQL [materialized views](https://www.postgresql.org/docs/current/rules-materializedviews.html). The file `src/materialize.js` contains functions to update derived tables. A materialization consists of: - A *table model*, as described above. - A *SQL View*. - A description of which tables the SQL view depends on. Some materialiations can be computed *incrementally*. In order for this to happen, it is necessary to compute the set of dirty rows - rows that may potentially be modified. This is not possible to do efficiently in the general case, but there is support for doing it in the cases where the derived table contains a foreign key reference to the table it is derived from. A derived table may have both incremental and non-incremental dependencies. An incremental dependency is possible if and only if there is a forein key relation between the derived table and the dependency table. The foreign key dependency is part of the materialization model. --- _Source: https://npm.io/package/@dawadk/import-util · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._