2e:null 2f:["$","title",null,{"children":"Schema Generator for synchronizing external state into BigQuery | Tom Larkworthy | Observable"}] 34:T9b5,md` This is a design pattern for synchronizing BigQuery with external state with low latency. Its a situation I have run into enough to motivate building a generator for. This tool generates the right statements for creating custom tables and views for your particular situation and data types. ![Architecture diagram](https://raw.githubusercontent.com/tomlarkworthy/observablehq_data/master/images/BQSync.png) The pattern achieves synchronization by streaming key-value mutations to an append only log. This is useful for data like user preferences or other externally managed relations etc, where the rate of change is relatively low, but there is a lot of static data and you want the changes reflected in the data pipelines as fast as possible, with more than 1000 updates per day. It is an alternative to replacing a table in a periodic batch process, which often is wasteful and adds significant latency. The general idea is we represent the external data as a key-value relation within BigQuery. This abstraction is really a view of an append only oplog of assignment operations. It is the oplog that external sources write to in an append only real time fashion. The downstream bigquery consumers attach to a logical view of the oplog. So, to update a single key-value record, the new key-value pair is inserted, with the current timestamp, to the oplog table. This is then reflected in the logical view almost immediately. This tool is a code generator that produces right SQL for your project, dataset, KV name and column names and datatypes, so you can give your fields more meaningful names and support even structs as values. Feel free to [fork and customize](https://observablehq.com/@tomlarkworthy/synchronizing-external-state-into-bigquery-efficiently)! MIT licensed. The outline of the setup is 1. Create an oplog table to store key-value mutations 2. Create a view of that that summarizes the latest state. 3. Run a background compaction query that prunes old operations from the oplog to prevent unbounded growth. ## Instructions Use the tool's form below to customize the names and datatypes of the keys and values. For example, you might have a user-organization relation held in an external user management system. In this case the key might be userId as a STRING, and the value is the organization as a STRING. Whenever a user is created in an organization, append (userId, organizationId, CURRENT_TIMESTAMP) into the user_organization_oplog table. `35:T7ca, md` ## Oplog Storage Table The following code will regenerate the oplog table. External services should stream inserts into this to update individual Key-Value records. Run as a [query](https://bigquery.cloud.google.com/queries/${config.projectId}) in the UI. \`\`\`sql #standardSQL CREATE OR REPLACE TABLE \`${config.projectId}.${config.dataset}.${config.name}_oplog\` ( ${config.key} ${config.keyT} NOT NULL, ${config.value} ${config.valueT}, timestamp TIMESTAMP NOT NULL ) ${clusterBy} OPTIONS( description = "Inserts rows to update the ${config.key}-${config.value} relation. Last write wins as determined by the timestamp." ); \`\`\` ## Key-Value View of Oplog The following SQL creates a view of the oplog of the latest values of each Key-Value assignment. Downstream BQ consumers should query this view rather than the oplog. Run as a [query](https://bigquery.cloud.google.com/queries/${config.projectId}) in the UI. \`\`\`sql #standardSQL CREATE OR REPLACE VIEW \`${config.projectId}.${config.dataset}.${config.name}\` AS SELECT ${config.key}, ${config.value}, timestamp AS last_modified FROM \`${config.projectId}.${config.dataset}.${config.name}_oplog\` ops1 WHERE timestamp = ( SELECT MAX(timestamp) FROM \`${config.projectId}.${config.dataset}.${config.name}_oplog\` ops2 WHERE ops1.${config.key} = ops2.${config.key} ) AND ${config.value} IS NOT NULL; \`\`\` ## Periodic Inplace Oplog Truncation The following query should be run in a [scheduled query](https://bigquery.cloud.google.com/scheduledqueries/${config.projectId}) to compact the oplog regularly and keep the oplog small. \`\`\`sql #standardSQL MERGE \`${config.projectId}.${config.dataset}.${config.name}_oplog\` ops USING (SELECT * FROM \`${config.projectId}.${config.dataset}.${config.name}\`) kv ON TRUE AND ops.${config.key} = kv.${config.key} AND ops.${config.value} = kv.${config.value} AND ops.timestamp = kv.last_modified WHEN NOT MATCHED BY SOURCE THEN DELETE \`\`\` `36:T41f,`Copyright 2019 Tom Larkworthy Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.`37:[] 32:{"id":"4f5c11416c999120","slug":"synchronizing-external-state-into-bigquery-efficiently","title":"Schema Generator for synchronizing external state into BigQuery","thumbnail":"324efee9e73f99fb7bd894f7af1cbed3e0ca95cb19b5a8830dfed7407f1d4b78","access_level":"public","create_time":"$D2019-05-10T07:32:28.791Z","update_time":"$D2020-10-13T18:16:39.230Z","view_time":null,"first_public_time":"$D2019-05-10T15:48:33.680Z","has_unpublished_edits":null,"fork":false,"stars":3,"starred":false,"owner":{"type":"team","id":"7db5ed2b0697d645","login":"tomlarkworthy","name":"Tom Larkworthy","avatar_url":"https://avatars-next.observableusercontent.com/47327a8bc1966f2186dcb3ebf4b7ee6e4e7ab9a5c2a07405aff57200ea778f71","tier":"starter_2024"},"version":203,"publish_version":203,"role":null,"authored":false,"authors":[{"type":"individual","id":"5215f6ec4a999d40","login":"tomlarkworthy","name":"Tom Larkworthy","avatar_url":"https://avatars-next.observableusercontent.com/47327a8bc1966f2186dcb3ebf4b7ee6e4e7ab9a5c2a07405aff57200ea778f71","tier":null}],"members":[{"type":"individual","id":"5215f6ec4a999d40","login":"tomlarkworthy","name":"Tom Larkworthy","avatar_url":"https://avatars-next.observableusercontent.com/47327a8bc1966f2186dcb3ebf4b7ee6e4e7ab9a5c2a07405aff57200ea778f71","tier":null,"role":"editor"}],"comments":[],"custom_thumbnail":false,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":null,"type":"notebook","body":{"title":"Schema Generator for synchronizing external state into BigQuery","cells":[{"id":195,"value":"md`\n# Schema Generator for synchronizing external state into BigQuery\n`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":0,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":2,"value":"viewof config = form(html``)","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":30,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":80,"value":"clusterBy = config.cluster? `CLUSTER BY ${config.cluster}` : \"\";","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":144,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":5,"value":"import {form} from \"@mbostock/form-input\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":186,"value":"import {futurice_profile} from \"@tomlarkworthy/tom-larkworthy\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":189,"value":"futurice_profile","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W37","stdlib":"1","titling":"auto"}} 33:"$32:body"