Community Articles
Find and share helpful community-sourced technical articles
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.
Labels (1)
Super Guru

This is an update to a previous article on accessing Philadelphia Open Crime Data and storing it in Apache Phoenix on HBase.

Part 1: https://community.hortonworks.com/articles/54947/reading-opendata-json-and-storing-into-phoenix-tab....

For nosql summit

https://community.hortonworks.com/articles/56642/creating-a-spring-boot-java-8-microservice-to-read....

Update this

http://princeton0.field.hortonworks.com:16010/master-status

https://community.hortonworks.com/content/kbentry/54947/reading-opendata-json-and-storing-into-phoen...

Philly Crime Data

City of Philly

App Token

76MVJDcTksxeS1uYPf8D0XdUF

Secret Token

WZnlB_YJ5r9rjj_alWVdc_yqnxRpnIk5BHgb

crime

https://data.phila.gov/resource/sspu-uyfa.json?$$app_token=76MVJDcTksxeS1uYPf8D0XdUF&$limit=5000

https://www.opendataphilly.org/dataset/crime-incidents/resource/f6e5998d-9d33-4a45-8397-3a6bb8607d10

https://www.opendataphilly.org/dataset/crime-incidents

https://data.phila.gov/resource/sspu-uyfa.json


https://data.phila.gov/resource/sspu-uyfa.json?$$app_token=76MVJDcTksxeS1uYPf8D0XdUF&$limit=5000&dis...



"dc_dist":"18",

"dc_key":"200918067518",

"dispatch_date":"2009-10-02",

"dispatch_date_time":"2009-10-02T14:24:00.000",

"dispatch_time":"14:24:00",

"hour":"14",

"location_block":"S 38TH ST / MARKETUT ST",

"psa":"3",

"text_general_code":"Other Assaults",

"ucr_general":"800"}

CREATE EXTERNAL TABLE

crime (dc_dist STRING,

dc_key STRING,dispatch_date STRING,dispatch_date_time STRING,hour STRING,location_block STRING,psa STRING,

text_general_code STRING,ucr_general STRING)

CLUSTERED BY (psa)INTO 4 BUCKETS

ROW FORMAT DELIMITED

STORED AS ORC

LOCATION '/crime/hive'

TBLPROPERTIES('transactional'='true');

dc_dist,dc_key ,dispatch_date,dispatch_date_time,hour,location_block,psa,text_general_code,ucr_general

Today

https://data.phila.gov/resource/sspu-uyfa.json?$$app_token=76MVJDcTksxeS1uYPf8D0XdUF&$limit=5000&dis...

&dispatch_date=${now():format('yyyy-MM-dd')}

311 service

https://data.phila.gov/resource/4t9v-rppq.json?$$app_token=76MVJDcTksxeS1uYPf8D0XdUF&$limit=5000

http://arcgis.dvrpc.org/arcgis/rest/services/Transportation/PedestrianCounts/FeatureServer/1

http://arcgis.dvrpc.org/arcgis/rest/services/Transportation/TrafficCounts/FeatureServer/0

see: https://community.hortonworks.com/articles/52856/stream-data-into-hive-like-a-king-using-nifi.html

Perhaps adapt this

https://github.com/socrata/soda-java

https://www.opendataphilly.org/

create table crime

hdfs dfs -mkdir -p /crime/fail

hdfs dfs -mkdir -p /crime

https://phoenix.apache.org/faq.html

/usr/hdp/current/phoenix-client/bin/sqlline.py localhost:2181:/hbase-unsecure

CREATE TABLE phillycrime (dc_dist varchar,

dc_key varchar not null primary key,dispatch_date varchar,dispatch_date_time varchar,dispatch_time varchar,hour varchar,location_block varchar,psa varchar,

text_general_code varchar,ucr_general varchar);

{"dc_dist":"18","dc_key":"200918067518","dispatch_date":"2009-10-02","dispatch_date_time":"2009-10-02T14:24:00.000","dispatch_time":"14:24:00","hour":"14","location_block":"S 38TH ST / MARKETUT ST","psa":"3","text_general_code":"Other Assaults","ucr_general":"800"}

upsert into phillycrime values ('18', '200918067518', '2009-10-02','2009-10-02T14:24:00.000','14:24:00','14', 'S 38TH ST / MARKETUT ST','3','Other Assaults','800');

!tables

!describe phillycrime

upsert into test values

org.apache.phoenix.jdbc.PhoenixDriver

jdbc:phoenix:localhost:2181:/hbase-unsecure

/usr/hdp/2.4.0.0-169/phoenix/phoenix-client.jar

/usr/hdp/2.4.0.0-169/hbase/lib/hbase-client.jar

/etc/hbase/conf/hbase-site.xml

plus hadoop ones

https://community.hortonworks.com/articles/19016/connect-to-phoenix-hbase-using-dbvisualizer.html

0: jdbc:phoenix:localhost:2181:/hbase-unsecur> !describe phillycrime

+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+----------------+

| TABLE_CAT | TABLE_SCHEM | TABLE_NAME | COLUMN_NAME | |

+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+----------------+

| | | PHILLYCRIME | DC_DIST | 12 |

| | | PHILLYCRIME | DC_KEY | 12 |

| | | PHILLYCRIME | DISPATCH_DATE | 12 |

| | | PHILLYCRIME | DISPATCH_DATE_TIME | 12 |

| | | PHILLYCRIME | HOUR | 12 |

| | | PHILLYCRIME | LOCATION_BLOCK | 12 |

| | | PHILLYCRIME | PSA | 12 |

| | | PHILLYCRIME | TEXT_GENERAL_CODE | 12 |

| | | PHILLYCRIME | UCR_GENERAL | 12 |

+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+----------------+

0: jdbc:phoenix:localhost:2181:/hbase-unsecur>

0: jdbc:phoenix:localhost:2181:/hbase-unsecur> upsert into phillycrime values ('18', '200918067518', '2009-10-02','2009-10-02T14:24:00.000','14', 'S 38TH ST / MARKETUT ST','3','Other Assaults','800');

1 row affected (0.115 seconds)

0: jdbc:phoenix:localhost:2181:/hbase-unsecur> select * from phillycrime;

+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+----------------+

| DC_DIST | DC_KEY | DISPATCH_DATE | DISPATCH_DATE_TIME | |

+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+----------------+

| 18 | 200918067518 | 2009-10-02 | 2009-10-02T14:24:00.000 | 14 |

+------------------------------------------+------------------------------------------+------------------------------------------+------------------------------------------+----------------+

1 row selected (0.197 seconds)

upsert into phillycrime values ('${'dc_dist'}','${'dc_key'}','${'dispatch_date'}','${'dispatch_date_time'}','${'dispatch_time'}','${'hour'}','${'location_block'}','${'psa'}','${'text_general_code'}','${'ucr_general'}')

jdbc:phoenix:localhost:2181:/hbase-unsecur> select distinct(text_general_code) from phillycrime;



https://data.phila.gov/resource/sspu-uyfa.json?$$$app_token=76MVJDcTksxeS1uYPf8D0XdUF&$$limit=5000&d...

<value>phillycrime${now():toNumber()}.json</value>

upsert into phillycrime values ('${'dc_dist'}','${'dc_key'}','${'dispatch_date'}','${'dispatch_date_time'}','${'dispatch_time'}','${'hour'}','${'location_block'}','${'psa'}','${'text_general_code'}','${'ucr_general'}')

<name>URL</name>

<value>https://data.phila.gov/resource/sspu-uyfa.json?$$$app_token=76MVJDcTksxeS1uYPf8D0XdUF&$$limit=5000&dispatch_date=${now():format('yyyy-MM-dd')}</value>

</property>

<property>

<name>Filename</name>

<value>phillycrime${now():toNumber()}.json</value>

</property>

<name>Regular Expression</name>

<value>(?s:^.*$)</value>

</property>

<property>

<name>Replacement Value</name>

<value>upsert into phillycrime values ('${'dc_dist'}','${'dc_key'}','${'dispatch_date'}','${'dispatch_date_time'}','${'dispatch_time'}','${'hour'}','${'location_block'}','${'psa'}','${'text_general_code'}','${'ucr_general'}')</value>

<comment/>

<class>org.apache.nifi.dbcp.DBCPConnectionPool</class>

<enabled>true</enabled>

<property>

<name>Database Connection URL</name>

<value>jdbc:phoenix:localhost:2181:/hbase-unsecure</value>

</property>

<property>

<name>Database Driver Class Name</name>

<value>org.apache.phoenix.jdbc.PhoenixDriver</value>

</property>

<property>

<name>Database Driver Jar Url</name>

<value>file:///usr/hdp/2.4.0.0-169/phoenix/phoenix-client.jar</value>

https://data.phila.gov/resource/sspu-uyfa.json

https://www.opendataphilly.org/dataset/crime-incidents

Run spring boot …

https://www.opendataphilly.org/dataset/crime-incidents

https://community.hortonworks.com/articles/72420/ingesting-remote-sensor-feeds-into-apache-phoenix.h...

https://github.com/tspannhw/phoenix




org.apache.phoenix.jdbc.PhoenixDriver




jdbc:phoenix:princeton0.field.hortonworks.com:/hbase-unsecure



phoenixuser

254 Views
0 Kudos
Don't have an account?
Coming from Hortonworks? Activate your account here
Version history
Revision #:
1 of 1
Last update:
‎02-10-2019 10:15 PM
Updated by:
 
Contributors
Top Kudoed Authors