{"action":"created","comment":{"url":"https://api.github.com/repos/citusdata/citus_docs/pulls/comments/75875114","id":75875114,"diff_hunk":"@@ -0,0 +1,422 @@\n+.. _introduction:\n+\n+Real Time Dashboards\n+#####################\n+\n+Citus provides real-time queries over large datasets. One workload we commonly see at\n+Citus involves powering real-time dashboards of event data.\n+\n+For example, you could be a cloud services provider helping other businesses monitor their\n+HTTP traffic. Every time one of your clients receives an HTTP request your service\n+receives a log record. You want to ingest all those records and create an HTTP analytics\n+dashboard which gives your clients insights such as the number HTTP errors their sites\n+served. It's important that this data shows up with as little latency as possible so your\n+clients can fix problems with their sites. It's also important for the dashboard to show\n+graphs of historical trends.\n+\n+Alternatively, maybe you're building an advertising network and want to show clients\n+clickthrough rates on their campaigns. In this example latency is also critical, raw data\n+volume is also high, and both historical and live data are important.\n+\n+In this technical solution we'll demonstrate how to build part of the first example,\n+but this architecture would work equally well for the second and many other use-cases.\n+\n+Running It Yourself\n+-------------------\n+\n+There will be code snippets in this tutorial but they don't specify a complete system.\n+There's `a github repo <https://github.com/citusdata/realtime-dashboards-resources>`_ with\n+all the details in one place. If you've followed our installation instructions for running\n+Citus on either a single or multiple machines you're ready to try it out.\n+\n+Data Model\n+----------\n+\n+The data we're dealing with is an immutable stream of log data. We'll insert directly into\n+Citus but it's also common for this data to first be routed through something like Kafka.\n+Doing so has the usual advantages, and makes it easier to pre-aggregate the data once data\n+volumes become unmanageably high.\n+\n+We'll use a simple schema for ingesting HTTP event data. This schema serves as an example\n+to demonstrate the overall architecture; a real system might use additional columns.\n+\n+.. code-block:: sql\n+\n+  -- this is run on the master\n+  CREATE TABLE http_request (\n+    site_id INT,\n+    ingest_time TIMESTAMPTZ DEFAULT now(),\n+\n+    url TEXT,\n+    request_country TEXT,\n+    ip_address INET,\n+\n+    status_code INT,\n+    response_time_msec INT\n+  );\n+  SELECT master_create_distributed_table('http_request', 'site_id', 'hash');\n+  SELECT master_create_worker_shards('http_request', 16, 2);\n+\n+When we call :ref:`master_create_distributed_table <master_create_distributed_table>`\n+we ask Citus to hash-distribute ``http_request`` using the ``site_id`` column. That means\n+all the data for a particular site will live in the same shard.\n+\n+When we call :ref:`master_create_worker_shards <master_create_worker_shards>` we tell\n+Citus to create 16 shards and 2 replicas of each shard (for a total of 32 shard replicas).\n+We recommend :ref:`using 2-4x as many shards <faq_choose_shard_count>` as CPU cores in\n+your cluster. Using this many shards lets you rebalance data across your cluster after\n+adding new worker nodes.\n+\n+Using a replication factor of 2 means every shard is held on multiple workers. When a\n+worker fails the master will prevent downtime by serving queries for that worker's shards\n+using the other replicas.\n+\n+.. NOTE::\n+\n+  In Citus Cloud you must use a replication factor of 1 (instead of the 2 used here). As\n+  Citus Cloud uses `streaming replication\n+  <https://www.postgresql.org/docs/current/static/warm-standby.html>`_ to achieve high\n+  availability maintaining shard replicas would be redundant.\n+\n+With this, the system is ready to accept data and serve queries! We've provided `a data\n+ingest script\n+<https://github.com/citusdata/realtime-dashboards-resources/blob/master/ingest_example_data.sql>`_\n+you can run to generate example data. Once you've ingested data, you can run dashboard\n+queries such as:\n+\n+.. code-block:: sql\n+\n+  SELECT\n+    date_trunc('minute', ingest_time) as minute,\n+    COUNT(1) AS request_count,\n+    SUM(CASE WHEN (status_code between 200 and 299) THEN 1 ELSE 0 END) as success_count,\n+    SUM(CASE WHEN (status_code between 200 and 299) THEN 0 ELSE 1 END) as error_count,\n+    SUM(response_time_msec) / COUNT(1) AS average_response_time_msec\n+  FROM http_request\n+  WHERE zone_id = 1 AND minute > date_trunc('minute', now()) - interval '5 minutes'\n+  GROUP BY minute;\n+ \n+The setup described above works, but has two drawbacks:\n+\n+* Your HTTP analytics dashboard must go over each row every time it needs to generate a\n+  graph. For example, if your clients are interested in trends over the past year, your\n+  queries will aggregate every row for the past year from scratch.\n+* Your storage costs will grow proportionally with the ingest rate and the length of the\n+  queryable history. In practice, you may want to keep raw events for a shorter period of\n+  time (one month) and look at historical graphs over a longer time period (years).\n+\n+Rollups\n+-------\n+\n+You can overcome both drawbacks by rolling up the raw data into a pre-aggregated form.\n+Here, we'll aggregate the raw data into a table which stores summaries of 1-minute\n+intervals. In a production system, you would probably also want something like 1-hour and\n+1-day intervals, these each correspond to zoom-levels in the dashboard. When the user\n+wants request times for the last month the dashboard can simply read and chart the values\n+for each of the last 30 days.\n+\n+.. code-block:: sql\n+\n+  CREATE TABLE http_request_1min (\n+        site_id INT,\n+        ingest_time TIMESTAMPTZ, -- which minute this row represents\n+\n+        error_count INT,\n+        success_count INT,\n+        request_count INT,\n+        average_response_time_msec INT,\n+        CHECK (request_count = error_count + success_count),\n+        CHECK (ingest_time = date_trunc('minute', ingest_time)),\n+  );\n+  SELECT master_create_distributed_table('http_requests_1min', 'site_id', 'hash');\n+  SELECT master_create_worker_shards('http_requests_1min', 16, 2);\n+  \n+  -- indexes aren't automatically created by Citus\n+  -- this will create the index on all shards\n+  CREATE INDEX ON http_requests_1min (zone_id, ingest_time);","path":"tech_soln/real_time_dashboards.rst","position":136,"original_position":136,"commit_id":"b09dec28e629ef6fddfeba526e295f3aaf57c46c","original_commit_id":"b09dec28e629ef6fddfeba526e295f3aaf57c46c","user":{"login":"byucesoy","id":2082070,"avatar_url":"https://avatars.githubusercontent.com/u/2082070?v=3","gravatar_id":"","url":"https://api.github.com/users/byucesoy","html_url":"https://github.com/byucesoy","followers_url":"https://api.github.com/users/byucesoy/followers","following_url":"https://api.github.com/users/byucesoy/following{/other_user}","gists_url":"https://api.github.com/users/byucesoy/gists{/gist_id}","starred_url":"https://api.github.com/users/byucesoy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/byucesoy/subscriptions","organizations_url":"https://api.github.com/users/byucesoy/orgs","repos_url":"https://api.github.com/users/byucesoy/repos","events_url":"https://api.github.com/users/byucesoy/events{/privacy}","received_events_url":"https://api.github.com/users/byucesoy/received_events","type":"User","site_admin":false},"body":"We need to give index name, otherwise citus complains: \"creating index without a name on a distributed table is currently unsupported\"","created_at":"2016-08-23T14:18:01Z","updated_at":"2016-08-23T14:18:01Z","html_url":"https://github.com/citusdata/citus_docs/pull/118#discussion_r75875114","pull_request_url":"https://api.github.com/repos/citusdata/citus_docs/pulls/118","_links":{"self":{"href":"https://api.github.com/repos/citusdata/citus_docs/pulls/comments/75875114"},"html":{"href":"https://github.com/citusdata/citus_docs/pull/118#discussion_r75875114"},"pull_request":{"href":"https://api.github.com/repos/citusdata/citus_docs/pulls/118"}}},"pull_request":{"url":"https://api.github.com/repos/citusdata/citus_docs/pulls/118","id":78876837,"html_url":"https://github.com/citusdata/citus_docs/pull/118","diff_url":"https://github.com/citusdata/citus_docs/pull/118.diff","patch_url":"https://github.com/citusdata/citus_docs/pull/118.patch","issue_url":"https://api.github.com/repos/citusdata/citus_docs/issues/118","number":118,"state":"open","locked":false,"title":"[WIP] Reference architecture one","user":{"login":"lithp","id":466333,"avatar_url":"https://avatars.githubusercontent.com/u/466333?v=3","gravatar_id":"","url":"https://api.github.com/users/lithp","html_url":"https://github.com/lithp","followers_url":"https://api.github.com/users/lithp/followers","following_url":"https://api.github.com/users/lithp/following{/other_user}","gists_url":"https://api.github.com/users/lithp/gists{/gist_id}","starred_url":"https://api.github.com/users/lithp/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lithp/subscriptions","organizations_url":"https://api.github.com/users/lithp/orgs","repos_url":"https://api.github.com/users/lithp/repos","events_url":"https://api.github.com/users/lithp/events{/privacy}","received_events_url":"https://api.github.com/users/lithp/received_events","type":"User","site_admin":false},"body":"do not merge, this is only open so we can collect comments","created_at":"2016-07-26T15:50:16Z","updated_at":"2016-08-23T14:18:01Z","closed_at":null,"merged_at":null,"merge_commit_sha":"1a200a4ddcff99ab964bfdb5dbad12f021078ed1","assignee":{"login":"ozgune","id":2545443,"avatar_url":"https://avatars.githubusercontent.com/u/2545443?v=3","gravatar_id":"","url":"https://api.github.com/users/ozgune","html_url":"https://github.com/ozgune","followers_url":"https://api.github.com/users/ozgune/followers","following_url":"https://api.github.com/users/ozgune/following{/other_user}","gists_url":"https://api.github.com/users/ozgune/gists{/gist_id}","starred_url":"https://api.github.com/users/ozgune/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ozgune/subscriptions","organizations_url":"https://api.github.com/users/ozgune/orgs","repos_url":"https://api.github.com/users/ozgune/repos","events_url":"https://api.github.com/users/ozgune/events{/privacy}","received_events_url":"https://api.github.com/users/ozgune/received_events","type":"User","site_admin":false},"assignees":[{"login":"ozgune","id":2545443,"avatar_url":"https://avatars.githubusercontent.com/u/2545443?v=3","gravatar_id":"","url":"https://api.github.com/users/ozgune","html_url":"https://github.com/ozgune","followers_url":"https://api.github.com/users/ozgune/followers","following_url":"https://api.github.com/users/ozgune/following{/other_user}","gists_url":"https://api.github.com/users/ozgune/gists{/gist_id}","starred_url":"https://api.github.com/users/ozgune/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ozgune/subscriptions","organizations_url":"https://api.github.com/users/ozgune/orgs","repos_url":"https://api.github.com/users/ozgune/repos","events_url":"https://api.github.com/users/ozgune/events{/privacy}","received_events_url":"https://api.github.com/users/ozgune/received_events","type":"User","site_admin":false}],"milestone":null,"commits_url":"https://api.github.com/repos/citusdata/citus_docs/pulls/118/commits","review_comments_url":"https://api.github.com/repos/citusdata/citus_docs/pulls/118/comments","review_comment_url":"https://api.github.com/repos/citusdata/citus_docs/pulls/comments{/number}","comments_url":"https://api.github.com/repos/citusdata/citus_docs/issues/118/comments","statuses_url":"https://api.github.com/repos/citusdata/citus_docs/statuses/b09dec28e629ef6fddfeba526e295f3aaf57c46c","head":{"label":"citusdata:reference-architecture-one","ref":"reference-architecture-one","sha":"b09dec28e629ef6fddfeba526e295f3aaf57c46c","user":{"login":"citusdata","id":2545424,"avatar_url":"https://avatars.githubusercontent.com/u/2545424?v=3","gravatar_id":"","url":"https://api.github.com/users/citusdata","html_url":"https://github.com/citusdata","followers_url":"https://api.github.com/users/citusdata/followers","following_url":"https://api.github.com/users/citusdata/following{/other_user}","gists_url":"https://api.github.com/users/citusdata/gists{/gist_id}","starred_url":"https://api.github.com/users/citusdata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/citusdata/subscriptions","organizations_url":"https://api.github.com/users/citusdata/orgs","repos_url":"https://api.github.com/users/citusdata/repos","events_url":"https://api.github.com/users/citusdata/events{/privacy}","received_events_url":"https://api.github.com/users/citusdata/received_events","type":"Organization","site_admin":false},"repo":{"id":56884660,"name":"citus_docs","full_name":"citusdata/citus_docs","owner":{"login":"citusdata","id":2545424,"avatar_url":"https://avatars.githubusercontent.com/u/2545424?v=3","gravatar_id":"","url":"https://api.github.com/users/citusdata","html_url":"https://github.com/citusdata","followers_url":"https://api.github.com/users/citusdata/followers","following_url":"https://api.github.com/users/citusdata/following{/other_user}","gists_url":"https://api.github.com/users/citusdata/gists{/gist_id}","starred_url":"https://api.github.com/users/citusdata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/citusdata/subscriptions","organizations_url":"https://api.github.com/users/citusdata/orgs","repos_url":"https://api.github.com/users/citusdata/repos","events_url":"https://api.github.com/users/citusdata/events{/privacy}","received_events_url":"https://api.github.com/users/citusdata/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/citusdata/citus_docs","description":"Citus Documentation","fork":false,"url":"https://api.github.com/repos/citusdata/citus_docs","forks_url":"https://api.github.com/repos/citusdata/citus_docs/forks","keys_url":"https://api.github.com/repos/citusdata/citus_docs/keys{/key_id}","collaborators_url":"https://api.github.com/repos/citusdata/citus_docs/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/citusdata/citus_docs/teams","hooks_url":"https://api.github.com/repos/citusdata/citus_docs/hooks","issue_events_url":"https://api.github.com/repos/citusdata/citus_docs/issues/events{/number}","events_url":"https://api.github.com/repos/citusdata/citus_docs/events","assignees_url":"https://api.github.com/repos/citusdata/citus_docs/assignees{/user}","branches_url":"https://api.github.com/repos/citusdata/citus_docs/branches{/branch}","tags_url":"https://api.github.com/repos/citusdata/citus_docs/tags","blobs_url":"https://api.github.com/repos/citusdata/citus_docs/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/citusdata/citus_docs/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/citusdata/citus_docs/git/refs{/sha}","trees_url":"https://api.github.com/repos/citusdata/citus_docs/git/trees{/sha}","statuses_url":"https://api.github.com/repos/citusdata/citus_docs/statuses/{sha}","languages_url":"https://api.github.com/repos/citusdata/citus_docs/languages","stargazers_url":"https://api.github.com/repos/citusdata/citus_docs/stargazers","contributors_url":"https://api.github.com/repos/citusdata/citus_docs/contributors","subscribers_url":"https://api.github.com/repos/citusdata/citus_docs/subscribers","subscription_url":"https://api.github.com/repos/citusdata/citus_docs/subscription","commits_url":"https://api.github.com/repos/citusdata/citus_docs/commits{/sha}","git_commits_url":"https://api.github.com/repos/citusdata/citus_docs/git/commits{/sha}","comments_url":"https://api.github.com/repos/citusdata/citus_docs/comments{/number}","issue_comment_url":"https://api.github.com/repos/citusdata/citus_docs/issues/comments{/number}","contents_url":"https://api.github.com/repos/citusdata/citus_docs/contents/{+path}","compare_url":"https://api.github.com/repos/citusdata/citus_docs/compare/{base}...{head}","merges_url":"https://api.github.com/repos/citusdata/citus_docs/merges","archive_url":"https://api.github.com/repos/citusdata/citus_docs/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/citusdata/citus_docs/downloads","issues_url":"https://api.github.com/repos/citusdata/citus_docs/issues{/number}","pulls_url":"https://api.github.com/repos/citusdata/citus_docs/pulls{/number}","milestones_url":"https://api.github.com/repos/citusdata/citus_docs/milestones{/number}","notifications_url":"https://api.github.com/repos/citusdata/citus_docs/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/citusdata/citus_docs/labels{/name}","releases_url":"https://api.github.com/repos/citusdata/citus_docs/releases{/id}","deployments_url":"https://api.github.com/repos/citusdata/citus_docs/deployments","created_at":"2016-04-22T20:54:52Z","updated_at":"2016-07-18T12:38:12Z","pushed_at":"2016-08-23T11:06:27Z","git_url":"git://github.com/citusdata/citus_docs.git","ssh_url":"git@github.com:citusdata/citus_docs.git","clone_url":"https://github.com/citusdata/citus_docs.git","svn_url":"https://github.com/citusdata/citus_docs","homepage":null,"size":2143,"stargazers_count":5,"watchers_count":5,"language":"CSS","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":4,"mirror_url":null,"open_issues_count":64,"forks":4,"open_issues":64,"watchers":5,"default_branch":"master"}},"base":{"label":"citusdata:v5.1","ref":"v5.1","sha":"bbc13e34f407336022a3db9bc8a98bdee4abf8c4","user":{"login":"citusdata","id":2545424,"avatar_url":"https://avatars.githubusercontent.com/u/2545424?v=3","gravatar_id":"","url":"https://api.github.com/users/citusdata","html_url":"https://github.com/citusdata","followers_url":"https://api.github.com/users/citusdata/followers","following_url":"https://api.github.com/users/citusdata/following{/other_user}","gists_url":"https://api.github.com/users/citusdata/gists{/gist_id}","starred_url":"https://api.github.com/users/citusdata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/citusdata/subscriptions","organizations_url":"https://api.github.com/users/citusdata/orgs","repos_url":"https://api.github.com/users/citusdata/repos","events_url":"https://api.github.com/users/citusdata/events{/privacy}","received_events_url":"https://api.github.com/users/citusdata/received_events","type":"Organization","site_admin":false},"repo":{"id":56884660,"name":"citus_docs","full_name":"citusdata/citus_docs","owner":{"login":"citusdata","id":2545424,"avatar_url":"https://avatars.githubusercontent.com/u/2545424?v=3","gravatar_id":"","url":"https://api.github.com/users/citusdata","html_url":"https://github.com/citusdata","followers_url":"https://api.github.com/users/citusdata/followers","following_url":"https://api.github.com/users/citusdata/following{/other_user}","gists_url":"https://api.github.com/users/citusdata/gists{/gist_id}","starred_url":"https://api.github.com/users/citusdata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/citusdata/subscriptions","organizations_url":"https://api.github.com/users/citusdata/orgs","repos_url":"https://api.github.com/users/citusdata/repos","events_url":"https://api.github.com/users/citusdata/events{/privacy}","received_events_url":"https://api.github.com/users/citusdata/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/citusdata/citus_docs","description":"Citus Documentation","fork":false,"url":"https://api.github.com/repos/citusdata/citus_docs","forks_url":"https://api.github.com/repos/citusdata/citus_docs/forks","keys_url":"https://api.github.com/repos/citusdata/citus_docs/keys{/key_id}","collaborators_url":"https://api.github.com/repos/citusdata/citus_docs/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/citusdata/citus_docs/teams","hooks_url":"https://api.github.com/repos/citusdata/citus_docs/hooks","issue_events_url":"https://api.github.com/repos/citusdata/citus_docs/issues/events{/number}","events_url":"https://api.github.com/repos/citusdata/citus_docs/events","assignees_url":"https://api.github.com/repos/citusdata/citus_docs/assignees{/user}","branches_url":"https://api.github.com/repos/citusdata/citus_docs/branches{/branch}","tags_url":"https://api.github.com/repos/citusdata/citus_docs/tags","blobs_url":"https://api.github.com/repos/citusdata/citus_docs/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/citusdata/citus_docs/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/citusdata/citus_docs/git/refs{/sha}","trees_url":"https://api.github.com/repos/citusdata/citus_docs/git/trees{/sha}","statuses_url":"https://api.github.com/repos/citusdata/citus_docs/statuses/{sha}","languages_url":"https://api.github.com/repos/citusdata/citus_docs/languages","stargazers_url":"https://api.github.com/repos/citusdata/citus_docs/stargazers","contributors_url":"https://api.github.com/repos/citusdata/citus_docs/contributors","subscribers_url":"https://api.github.com/repos/citusdata/citus_docs/subscribers","subscription_url":"https://api.github.com/repos/citusdata/citus_docs/subscription","commits_url":"https://api.github.com/repos/citusdata/citus_docs/commits{/sha}","git_commits_url":"https://api.github.com/repos/citusdata/citus_docs/git/commits{/sha}","comments_url":"https://api.github.com/repos/citusdata/citus_docs/comments{/number}","issue_comment_url":"https://api.github.com/repos/citusdata/citus_docs/issues/comments{/number}","contents_url":"https://api.github.com/repos/citusdata/citus_docs/contents/{+path}","compare_url":"https://api.github.com/repos/citusdata/citus_docs/compare/{base}...{head}","merges_url":"https://api.github.com/repos/citusdata/citus_docs/merges","archive_url":"https://api.github.com/repos/citusdata/citus_docs/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/citusdata/citus_docs/downloads","issues_url":"https://api.github.com/repos/citusdata/citus_docs/issues{/number}","pulls_url":"https://api.github.com/repos/citusdata/citus_docs/pulls{/number}","milestones_url":"https://api.github.com/repos/citusdata/citus_docs/milestones{/number}","notifications_url":"https://api.github.com/repos/citusdata/citus_docs/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/citusdata/citus_docs/labels{/name}","releases_url":"https://api.github.com/repos/citusdata/citus_docs/releases{/id}","deployments_url":"https://api.github.com/repos/citusdata/citus_docs/deployments","created_at":"2016-04-22T20:54:52Z","updated_at":"2016-07-18T12:38:12Z","pushed_at":"2016-08-23T11:06:27Z","git_url":"git://github.com/citusdata/citus_docs.git","ssh_url":"git@github.com:citusdata/citus_docs.git","clone_url":"https://github.com/citusdata/citus_docs.git","svn_url":"https://github.com/citusdata/citus_docs","homepage":null,"size":2143,"stargazers_count":5,"watchers_count":5,"language":"CSS","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":4,"mirror_url":null,"open_issues_count":64,"forks":4,"open_issues":64,"watchers":5,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/citusdata/citus_docs/pulls/118"},"html":{"href":"https://github.com/citusdata/citus_docs/pull/118"},"issue":{"href":"https://api.github.com/repos/citusdata/citus_docs/issues/118"},"comments":{"href":"https://api.github.com/repos/citusdata/citus_docs/issues/118/comments"},"review_comments":{"href":"https://api.github.com/repos/citusdata/citus_docs/pulls/118/comments"},"review_comment":{"href":"https://api.github.com/repos/citusdata/citus_docs/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/citusdata/citus_docs/pulls/118/commits"},"statuses":{"href":"https://api.github.com/repos/citusdata/citus_docs/statuses/b09dec28e629ef6fddfeba526e295f3aaf57c46c"}}}}
{"actor":{"display_login":"byucesoy"}}