{"action":"created","issue":{"url":"https://api.github.com/repos/pachyderm/pachyderm/issues/3066","repository_url":"https://api.github.com/repos/pachyderm/pachyderm","labels_url":"https://api.github.com/repos/pachyderm/pachyderm/issues/3066/labels{/name}","comments_url":"https://api.github.com/repos/pachyderm/pachyderm/issues/3066/comments","events_url":"https://api.github.com/repos/pachyderm/pachyderm/issues/3066/events","html_url":"https://github.com/pachyderm/pachyderm/issues/3066","id":343230008,"node_id":"MDU6SXNzdWUzNDMyMzAwMDg=","number":3066,"title":"Distributed SQL Support","user":{"login":"sjezewski","id":597953,"node_id":"MDQ6VXNlcjU5Nzk1Mw==","avatar_url":"https://avatars1.githubusercontent.com/u/597953?v=4","gravatar_id":"","url":"https://api.github.com/users/sjezewski","html_url":"https://github.com/sjezewski","followers_url":"https://api.github.com/users/sjezewski/followers","following_url":"https://api.github.com/users/sjezewski/following{/other_user}","gists_url":"https://api.github.com/users/sjezewski/gists{/gist_id}","starred_url":"https://api.github.com/users/sjezewski/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sjezewski/subscriptions","organizations_url":"https://api.github.com/users/sjezewski/orgs","repos_url":"https://api.github.com/users/sjezewski/repos","events_url":"https://api.github.com/users/sjezewski/events{/privacy}","received_events_url":"https://api.github.com/users/sjezewski/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"sjezewski","id":597953,"node_id":"MDQ6VXNlcjU5Nzk1Mw==","avatar_url":"https://avatars1.githubusercontent.com/u/597953?v=4","gravatar_id":"","url":"https://api.github.com/users/sjezewski","html_url":"https://github.com/sjezewski","followers_url":"https://api.github.com/users/sjezewski/followers","following_url":"https://api.github.com/users/sjezewski/following{/other_user}","gists_url":"https://api.github.com/users/sjezewski/gists{/gist_id}","starred_url":"https://api.github.com/users/sjezewski/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sjezewski/subscriptions","organizations_url":"https://api.github.com/users/sjezewski/orgs","repos_url":"https://api.github.com/users/sjezewski/repos","events_url":"https://api.github.com/users/sjezewski/events{/privacy}","received_events_url":"https://api.github.com/users/sjezewski/received_events","type":"User","site_admin":false},"assignees":[{"login":"sjezewski","id":597953,"node_id":"MDQ6VXNlcjU5Nzk1Mw==","avatar_url":"https://avatars1.githubusercontent.com/u/597953?v=4","gravatar_id":"","url":"https://api.github.com/users/sjezewski","html_url":"https://github.com/sjezewski","followers_url":"https://api.github.com/users/sjezewski/followers","following_url":"https://api.github.com/users/sjezewski/following{/other_user}","gists_url":"https://api.github.com/users/sjezewski/gists{/gist_id}","starred_url":"https://api.github.com/users/sjezewski/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sjezewski/subscriptions","organizations_url":"https://api.github.com/users/sjezewski/orgs","repos_url":"https://api.github.com/users/sjezewski/repos","events_url":"https://api.github.com/users/sjezewski/events{/privacy}","received_events_url":"https://api.github.com/users/sjezewski/received_events","type":"User","site_admin":false}],"milestone":null,"comments":4,"created_at":"2018-07-20T19:58:24Z","updated_at":"2018-07-21T01:12:08Z","closed_at":null,"author_association":"CONTRIBUTOR","body":"**What is the ask?**\r\n\r\nAs a user, I need to run large / complicated / unwieldly SQL queries on my data in Pachyderm\r\n\r\n---\r\n\r\nBroadly, the deliverable will look like the following. For the first pass, we'll assume postgres. Later on we'll add support for other adapters/formats.\r\n\r\n1) Basic Ingress Support\r\n\r\n- Update `put-file --split` to take `sql` as a delimiter option\r\n- This will take as an input a `pgdump` file, and break it up into smaller valid `pgdump` files (so that include the schema/table setup at the start) which contain a subset of the rows (via insert statements) of the original file (the count depending on the other `--split` flags specified)\r\n\r\n2) SQL engine sidecar\r\n\r\n- A lot of complexity was added to an internal prototype to try and handle postgres management and user code all in a single container\r\n- If you specify that the pipeline is a `sql` pipeline, it'll run this sidecar. Probably this will be an `adapter` field on the `transform` object. For now the only valid value will be `postgres`\r\n\r\n3) First class worker support\r\n\r\n- Once a pipeline is marked as having an `\"adapter\":\"postgres\"` the workers will ingest the data directly into the DB sidecar (not as files under `/pfs`)\r\n- We'll also need to supply fields on the transform so that a user can specify\r\n    - any SQL statements that they need to run (either a statement to run or a path to a file containing a stored proc)\r\n    - the select statement for grabbing the output\r\n- The output will be emitted as a CSV w headers\r\n- We'll also need to handle graceful setup/cleanup (esp in the event of user code failures) of the DB since it affects the long running postgres sidecar\r\n\r\n# Nice to Haves\r\n\r\nOut of scope / future iterations\r\n\r\n- Ingress Pipeline\r\n    - Cron ... periodically polls external database for ingress\r\n    - Keeps track of state ... either w limit/offset/from/cursor\r\n    - Configurable via secret (for DB URL/port/password/schema/table)\r\n    - Emits data in the format that is expected for the DB worker\r\n- Parallelism\r\n    - Since we have a long running postgres instance alongside the worker, we can parallelize datum processing ...\r\n    - If we make sure the DB uses namespaces\r\n- Adv config\r\n    - specify resource req/limits for the db sidecar separately (maybe this is once we have first class k8s manifests for pipelines)\r\n- More adapters\r\n    - MySQL / cockroach / etc\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/pachyderm/pachyderm/issues/comments/406759911","html_url":"https://github.com/pachyderm/pachyderm/issues/3066#issuecomment-406759911","issue_url":"https://api.github.com/repos/pachyderm/pachyderm/issues/3066","id":406759911,"node_id":"MDEyOklzc3VlQ29tbWVudDQwNjc1OTkxMQ==","user":{"login":"gabrielgrant","id":493968,"node_id":"MDQ6VXNlcjQ5Mzk2OA==","avatar_url":"https://avatars2.githubusercontent.com/u/493968?v=4","gravatar_id":"","url":"https://api.github.com/users/gabrielgrant","html_url":"https://github.com/gabrielgrant","followers_url":"https://api.github.com/users/gabrielgrant/followers","following_url":"https://api.github.com/users/gabrielgrant/following{/other_user}","gists_url":"https://api.github.com/users/gabrielgrant/gists{/gist_id}","starred_url":"https://api.github.com/users/gabrielgrant/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/gabrielgrant/subscriptions","organizations_url":"https://api.github.com/users/gabrielgrant/orgs","repos_url":"https://api.github.com/users/gabrielgrant/repos","events_url":"https://api.github.com/users/gabrielgrant/events{/privacy}","received_events_url":"https://api.github.com/users/gabrielgrant/received_events","type":"User","site_admin":false},"created_at":"2018-07-21T01:12:08Z","updated_at":"2018-07-21T01:12:08Z","author_association":"CONTRIBUTOR","body":"The one kinda tricky issue is that, although widely used, CSV is a pretty crappy format, with a ton of different subtly-different implementations, none of which have nearly the level of typing support that a SQL DB does (technically every value is a string). So we may also want to support an object-per-row stream of JSON objects:\r\n\r\n```\r\n{\"id\":1,\"column_1\":43,\"column_2\":\"asfasfasfd\"}\r\n{\"id\":2,\"column_1\":43,\"column_2\":\"asfasfasfd\"}\r\n...\r\n```\r\n\r\n(this is what is output by pg's `ROW_TO_JSON`, and already supported with our `put-file --split json`)"}}
{"actor":{"display_login":"gabrielgrant"}}