You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@kudu.apache.org by to...@apache.org on 2017/01/13 00:54:51 UTC
kudu git commit: docs: update quickstart to a new dataset
Repository: kudu
Updated Branches:
refs/heads/branch-1.2.x 97ad14d8a -> 32269bc76
docs: update quickstart to a new dataset
The old dataset we were using seems to have changed formats
and no longer has unique identifiers, etc.
The new quickstart uses the SF MTA dataset, which is a bit nicer because
it's (a) bigger, (b) more time-series-like, and (c) has an associated
real-time feed so we could expand this tutorial more later.
Change-Id: If27de6e1b3d1a6178e036cbe68fd6812d5f74a94
Reviewed-on: http://gerrit.cloudera.org:8080/5701
Reviewed-by: Jean-Daniel Cryans <jd...@apache.org>
Tested-by: Kudu Jenkins
(cherry picked from commit 77e38401c979062ee805e6246a697f751cc8123d)
Reviewed-on: http://gerrit.cloudera.org:8080/5705
Reviewed-by: Todd Lipcon <to...@apache.org>
Tested-by: Todd Lipcon <to...@apache.org>
Project: http://git-wip-us.apache.org/repos/asf/kudu/repo
Commit: http://git-wip-us.apache.org/repos/asf/kudu/commit/32269bc7
Tree: http://git-wip-us.apache.org/repos/asf/kudu/tree/32269bc7
Diff: http://git-wip-us.apache.org/repos/asf/kudu/diff/32269bc7
Branch: refs/heads/branch-1.2.x
Commit: 32269bc769c010c83e4b56a2e5110a041465384c
Parents: 97ad14d
Author: Todd Lipcon <to...@apache.org>
Authored: Thu Jan 12 15:09:50 2017 -0800
Committer: Todd Lipcon <to...@apache.org>
Committed: Fri Jan 13 00:54:12 2017 +0000
----------------------------------------------------------------------
docs/quickstart.adoc | 183 +++++++++++++++++++++-------------------------
1 file changed, 83 insertions(+), 100 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/kudu/blob/32269bc7/docs/quickstart.adoc
----------------------------------------------------------------------
diff --git a/docs/quickstart.adoc b/docs/quickstart.adoc
index 976d098..86c64d6 100644
--- a/docs/quickstart.adoc
+++ b/docs/quickstart.adoc
@@ -84,17 +84,29 @@ to consult the <<trouble, Troubleshooting>> section.
== Load Data
-To perform some typical operations with Kudu and Impala, you can load the
-http://www.flysfo.com/media/facts-statistics/air-traffic-statistics[SFO Passenger Data]
-into Impala and then load it into Kudu.
+To practice some typical operations with Kudu and Impala, we'll use the
+https://data.sfgov.org/Transportation/Raw-AVL-GPS-data/5fk7-ivit/data[San Francisco MTA
+GPS dataset]. This dataset contains raw location data transmitted periodically from
+sensors installed on the busses in the SF MTA's fleet.
-1. Upload the sample data from the home directory to HDFS.
+1. Download the sample data and load it into HDFS
++
+First we'll download the sample dataset, prepare it, and upload it into the HDFS
+cluster.
++
+The SF MTA's site is often a bit slow, so we've mirrored a sample CSV file from the
+dataset at http://kudu-sample-data.s3.amazonaws.com/sfmtaAVLRawData01012013.csv.gz
++
+The original dataset uses DOS-style line endings, so we'll convert it to
+UNIX-style during the upload process using `tr`.
+
[source,bash]
----
-$ hdfs dfs -mkdir /data
-$ hdfs dfs -put examples/SFO_Passenger_Data/MonthlyPassengerData_200507_to_201506.csv /data
+$ wget http://kudu-sample-data.s3.amazonaws.com/sfmtaAVLRawData01012013.csv.gz
+$ hdfs dfs -mkdir /sfmta
+$ zcat sfmtaAVLRawData01012013.csv.gz | tr -d '\r' | hadoop fs -put - /sfmta/data.csv
----
+
2. Create a new external Impala table to access the plain text data. To connect to Impala
in the virtual machine issue the following command:
+
@@ -107,137 +119,108 @@ Now, you can execute the following commands:
+
[source,sql]
----
-CREATE EXTERNAL TABLE passenger_data_raw (
- id int,
- activity_period int,
- operating_airline string,
- airline_iata_code string,
- published_airline string,
- published_airline_iata_code string,
- geo_summary string,
- geo_region string,
- activity_type_code string,
- price_category_code string,
- terminal string,
- boarding_area string,
- passenger_count bigint
+CREATE EXTERNAL TABLE sfmta_raw (
+ revision int,
+ report_time string,
+ vehicle_tag int,
+ longitude float,
+ latitude float,
+ speed float,
+ heading float
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
-LOCATION '/data/';
+LOCATION '/sfmta/'
+TBLPROPERTIES ('skip.header.line.count'='1');
----
+
3. Validate if the data was actually loaded run the following command:
+
[source,sql]
----
-SELECT count(*) FROM passenger_data_raw;
+SELECT count(*) FROM sfmta_raw;
+----------+
| count(*) |
+----------+
-| 13901 |
+| 859086 |
+----------+
----
+
-4. It's easy to convert data from any Hadoop file format and store it Kudu using the
-`CREATE TABLE AS SELECT` statement.
+4. Next we'll create a Kudu table and load the data. Note that we convert
+the string `report_time` field into a unix-style timestamp for more efficient
+storage.
+
[source,sql]
----
-CREATE TABLE passenger_data
-DISTRIBUTE BY HASH (id) INTO 16 BUCKETS
-TBLPROPERTIES(
-'storage_handler' = 'com.cloudera.kudu.hive.KuduStorageHandler',
-'kudu.table_name' = 'passenger_data',
-'kudu.master_addresses' = '127.0.0.1',
-'kudu.key_columns' = 'id'
- ) AS SELECT * FROM passenger_data_raw;
-
-+-----------------------+
-| summary |
-+-----------------------+
-| Inserted 13901 row(s) |
-+-----------------------+
-Fetched 1 row(s) in 1.26s
+CREATE TABLE sfmta (
+ report_time BIGINT NOT NULL,
+ vehicle_tag STRING NOT NULL,
+ longitude FLOAT NOT NULL,
+ latitude FLOAT NOT NULL,
+ speed FLOAT NOT NULL,
+ heading FLOAT NOT NULL,
+ PRIMARY KEY (report_time, vehicle_tag)
+)
+DISTRIBUTE BY HASH(report_time) INTO 8 BUCKETS
+STORED AS KUDU;
+
+INSERT INTO sfmta SELECT
+ UNIX_TIMESTAMP(report_time, 'MM/dd/yyyy HH:mm:ss'),
+ vehicle_tag,
+ longitude,
+ latitude,
+ speed,
+ heading
+FROM sfmta_raw;
+
+-- Modified 859086 row(s), 0 row error(s) in 8.55s
----
-[NOTE]
-====
-For `CREATE TABLE ... AS SELECT` we currently require that the first columns that are
-projected in the `SELECT` statement correspond to the Kudu table keys and are in the
-same order (`id` in the example above). If the default projection generated by `{asterisk}`
-does not meet this requirement, the user should avoid using `{asterisk}` and explicitly mention
-the columns to project, in the correct order.
-====
+
-The created table uses a simple single column primary key. See
+The created table uses a composite primary key. See
<<kudu_impala_integration.adoc#kudu_impala,Kudu Impala Integration>> for a more detailed
introduction to the extended SQL syntax for Impala.
-+
-The columns of the created table are copied from the `passenger_data_raw` base table. See
-http://www.cloudera.com/content/www/en-us/documentation/enterprise/latest/topics/impala_create_table.html[Impala's
-documentation] for more details about the extended SQL syntax for Impala.
== Read and Modify Data
Now that the data is stored in Kudu, you can run queries against it. The following query
-lists the airline with the highest passenger volume over the entire reporting timeframe.
+finds the data point containing the highest recorded vehicle speed.
[source,sql]
----
-SELECT sum(passenger_count) AS total, operating_airline FROM passenger_data
- GROUP BY operating_airline
- HAVING total IS NOT null
- ORDER BY total DESC LIMIT 10;
-
-+-----------+----------------------------------+
-| total | operating_airline |
-+-----------+----------------------------------+
-| 105363917 | United Airlines - Pre 07/01/2013 |
-| 51319845 | United Airlines |
-| 32657456 | SkyWest Airlines |
-| 31727343 | American Airlines |
-| 23801507 | Delta Air Lines |
-| 23685267 | Virgin America |
-| 22507320 | Southwest Airlines |
-| 16235520 | US Airways |
-| 11860630 | Alaska Airlines |
-| 6706438 | JetBlue Airways |
-+-----------+----------------------------------+
+SELECT * FROM sfmta ORDER BY speed DESC LIMIT 1;
+
++-------------+-------------+--------------------+-------------------+-------------------+---------+
+| report_time | vehicle_tag | longitude | latitude | speed | heading |
++-------------+-------------+--------------------+-------------------+-------------------+---------+
+| 1357022342 | 5411 | -122.3968811035156 | 37.76665878295898 | 68.33300018310547 | 82 |
++-------------+-------------+--------------------+-------------------+-------------------+---------+
----
-Looking at the result, you can already see a problem with the dataset. There is a
-duplicate airline name. Since the data is stored in Kudu rather than HDFS, you can quickly
-change any individual record and fix the problem without having to rewrite the entire
-table.
+With a quick link:https://www.google.com/search?q=122.3968811035156W+37.76665878295898N[Google search]
+we can see that this bus was traveling east on 16th street at 68MPH.
+At first glance, this seems unlikely to be true. Perhaps we do some research
+and find that this bus's sensor equipment was broken and we decide to
+remove the data. With Kudu this is very easy to correct using standard
+SQL:
[source,sql]
----
-UPDATE passenger_data
- SET operating_airline="United Airlines"
- WHERE operating_airline LIKE "United Airlines - Pre%";
-
-SELECT sum(passenger_count) AS total, operating_airline FROM passenger_data
- GROUP BY operating_airline
- HAVING total IS NOT null
- ORDER BY total DESC LIMIT 10;
-
-+-----------+--------------------+
-| total | operating_airline |
-+-----------+--------------------+
-| 156683762 | United Airlines |
-| 32657456 | SkyWest Airlines |
-| 31727343 | American Airlines |
-| 23801507 | Delta Air Lines |
-| 23685267 | Virgin America |
-| 22507320 | Southwest Airlines |
-| 16235520 | US Airways |
-| 11860630 | Alaska Airlines |
-| 6706438 | JetBlue Airways |
-| 6266220 | Northwest Airlines |
-+-----------+--------------------+
+DELETE FROM sfmta WHERE vehicle_tag = '5411';
+
+-- Modified 1169 row(s), 0 row error(s) in 0.25s
----
+== Next steps
+
+The above example showed how to load, query, and mutate a static dataset with Impala
+and Kudu. The real power of Kudu, however, is the ability to ingest and mutate data
+in a streaming fashion.
+
+As an exercise to learn the Kudu programmatic APIs, try implementing a program
+that uses the link:http://www.nextbus.com/xmlFeedDocs/NextBusXMLFeed.pdf[SFMTA
+XML data feed] to ingest this same dataset in real time into the Kudu table.
+
[[trouble]]
=== Troubleshooting