内容简介：This example (We will then integrate it with Splitgraph'sIn particular, we will be running a foreign data wrapper that queries the
Using a custom FDW with Splitgraph
This example ( source code on GitHub ) will show you how to write a customforeign data wrapper using Multicorn. Multicorn is a PostgreSQL extension that makes it possible to write foreign data wrappers in Python.
We will then integrate it with Splitgraph's
sgr mount
command. This will make it queryable from the Splitgraph engine and allow Splitgraph to use it inSplitfiles.
In particular, we will be running a foreign data wrapper that queries the Firebase Hacker News API . This will let us run SQL queries on the top, best and new stories from Hacker News, as well as Show HNs and Ask HNs.
This foreign data wrapper returns a list of rows from the remote database without performing any filtering. However, Multicorn also passes qualifiers and sort keys to the Python extension. This allows filtering to happen on the remote database as well, if it supports it.
To run this demo, you will need to have Docker and Docker Compose .
Clone the repository
Clone Splitgraph and go to the example project :
$ git clone https://github.com/splitgraph/splitgraph.git
$ cd splitgraph/examples/custom_fdw
The Compose stack consists of:
- a custom version of theSplitgraph engine (with the FDW installed);
-
a small Python container with the
sgrclient .
The source code for the foreign data wrapper is in src/hn_fdw/fdw.py . The mount handler that exposes it to Splitgraph is in src/hn_fdw/mount.py .
Start up the stack
Start the shell and initialize the engine. This will also build and start the engine container:
$ docker-compose run --rm sgr $ sgr init Initializing engine PostgresEngine LOCAL (sgr@engine:5432/splitgraph)... Database splitgraph already exists, skipping Ensuring the metadata schema at splitgraph_meta exists... Running splitgraph_meta--0.0.1.sql Running splitgraph_meta--0.0.1--0.0.2.sql Running splitgraph_meta--0.0.2--0.0.3.sql Installing Splitgraph API functions... Installing CStore management functions... Installing the audit trigger... Engine PostgresEngine LOCAL (sgr@engine:5432/splitgraph) initialized.
Check the help for the HN mount handler:
$ sgr mount hackernews --help
Usage: sgr mount hackernews [OPTIONS] SCHEMA
Mount a Hacker News story dataset using the Firebase API.
Options:
-c, --connection TEXT Connection string in the form
username:password@server:port
-o, --handler-options TEXT JSON-encoded dictionary of handler options:
endpoints: List of Firebase endpoints to mount,
mounted into the same tables as the endpoint
name. Supported endpoints:
{top,new,best,ask,show,job}stories.
--help Show this message and exit.
Mount the data and run some queries
Now, actually "mount" the dataset. This will create a series of foreign tables. When a PostgreSQL client queries these tables, the foreign data wrapper will forward the queries to the Firebase API:
$ sgr mount hackernews hackernews Mounting topstories... Mounting newstories... Mounting beststories... Mounting askstories... Mounting showstories... Mounting jobstories...
You can now run ordinary SQL queries against these tables:
$ sgr sql -s hackernews "SELECT id, title, url, score FROM topstories LIMIT 10" 23648942 Amazon to pay $1B+ for Zoox https://www.axios.com/report-amazon-to-pay-1-billion-for-self-driving-tech-firm-zoox-719d293b-3799-4315-a573-a226a58bb004.html 55 23646158 When you type realty.com into Safari it takes you to realtor.com https://www.facebook.com/story.php?story_fbid=10157161487396994&id=501751993 653 23648864 Turn recipe websites into plain text https://plainoldrecipe.com/ 30 23644253 Olympus quits camera business after 84 years https://www.bbc.com/news/technology-53165293 548 23648217 Boston bans use of facial recognition technology https://www.wbur.org/news/2020/06/23/boston-facial-recognition-ban 51 23646953 Curl Wttr.in https://github.com/chubin/wttr.in 190 23646164 Quora goes permanently remote-first https://twitter.com/adamdangelo/status/1276210618786168833 267 23646395 Dwarf Fortress Creator Explains Its Complexity and Origins [video] https://www.youtube.com/watch?v=VAhHkJQ3KgY 152 23645305 Blackballed by PayPal, Sci-Hub switches to Bitcoin https://www.coindesk.com/blackballed-by-paypal-scientific-paper-pirate-takes-bitcoin-donations 479 23646028 The Acorn Archimedes was the first desktop to use the ARM architecture https://spectrum.ieee.org/tech-talk/consumer-electronics/gadgets/why-wait-for-apple-try-out-the-original-arm-desktop-experience-today-with-a-raspberry-pi 111
PostgreSQL handles the actual query planning and filtering. The foreign data wrapper's job is to fetch records from the API. This setup supports any SQL syntax:
$ sgr sql -s hackernews "SELECT id, title, url, score FROM showstories ORDER BY score DESC LIMIT 5" 23643096 Show HN: Aviary.sh – A tiny Bash alternative to Ansible https://github.com/team-video/aviary.sh 235 23626167 Show HN: HN Deck – An alternative way to browse Hacker News https://hndeck.sagunshrestha.com/ 110 23640069 Show HN: Sourceful – Crowdsourcing the best public Google docs https://sourceful.co.uk 102 23627066 Show HN: Splitgraph - Build and share data with Postgres, inspired by Docker/Git http://www.splitgraph.com 79 23629125 Show HN: Deta – A cloud platform for building and deploying apps https://www.deta.sh/ 78
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
付费：互联网知识经济的兴起
方军 / 机械工业出版社 / 2017-6-1 / CNY 59.00
关于互联网知识付费的首部作品 知识工作正在被重塑，知识经济正在开启互联网时代下半场 为你展现互联网知识经济全景大图，解读新物种的前世今生 内容简介 一个产业解读 三个分析工具 一组知识卡片 书是最早的知识载体，已有2000多年的付费历史，随着移动互联网的普及，新的知识经 济在今天爆发，知识的创造者和传播者从书后走到了书前，互联网知识经济正在拉开帷幕。知识的......一起来看看 《付费：互联网知识经济的兴起》 这本书的介绍吧!
