内容简介：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
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Python语言程序设计
[美]梁勇(Lang Y. D.) / 李娜 / 机械工业出版社 / 2015-4 / 79.00元
本书采用“问题驱动”、“基础先行”和“实例和实践相结合”的方式，讲述如何使用Python语言进行程序设计。本书首先介绍Python程序设计的基本概念，接着介绍面向对象程序设计方法，最后介绍算法与数据结构方面的内容。为了帮助学生更好地掌握相关知识，本书每章都包括以下模块：学习目标，引言，关键点，检查点，问题，本章总结，测试题，编程题，注意、提示和警告。 本书可以作为高等院校计算机及相关专业Py......一起来看看 《Python语言程序设计》 这本书的介绍吧!
HTML 压缩/解压工具
在线压缩/解压 HTML 代码
XML、JSON 在线转换
在线XML、JSON转换工具