内容简介：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
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Linux从入门到精通
刘忆智、等 / 清华大学出版社 / 2010-1-1 / 59.00元
linux是目前增长最迅速的操作系统。本书由浅入深、循序渐进地向读者介绍linux的基本使用和系统管理。全书内容包括linux概述、linux安装、linux基本配置、桌面环境基本操作、shell基本命令、文件和目录管理、软件包管理、磁盘管理、用户与用户组管理、进程管理、网络配置、浏览网页、收发邮件、文件传输和共享、远程登录、多媒体应用、图像浏览和处理、打印机配置、办公软件的使用、linux编程工......一起来看看 《Linux从入门到精通》 这本书的介绍吧!
