Load csv by Cypher

Load csv by Cypher with Neo4j 3.0.6
refer : https://github.com/asakoRaven/livedoor-dataset

Transcript

1. Load csv by cypher Neo4j 3.0.6 2016.10.xx Asako TOYAMA
2. ໨త Neo4j cypher ͷֶश
3. Livedoor άϧϝ dataset Λ࢖͏ datasetͷઆ໌ livedoorάϧϝͷDataSetΛެ։ σʔλμ΢ϯϩʔυઌ https://github.com/livedoor/datasets
4. Livedoor άϧϝ Dataset ͓ళσʔλ restaurants.csv ޱίϛσʔλ ratings.csv ޱίϛ΁ͷ౤ථσʔλ ratings_vots.csv
5. Livedoor άϧϝ Dataset masterܥ ౎ಓ෎ݝϚελ prefs.csv ΤϦΞϚελ areas.csv ӺϚελ station.csv ΧςΰϦϚελ categories.csv ೔ຊྉཧͳͲϨετϥϯͷΧςΰϦ छผ
6. Modeling by arrows arrows ͱ͍͏͓ֆ͔͖πʔϧͰϞσϦϯάͯ͠ΈΔ ref. https://github.com/apcj/arrows
8. Relationshipͷ ໼ҹͷ޲͖Λ Ͳ͏ͨ͠Β͍͍͔ Θ͔Βͳ͍ʂ ޙͰมߋՄೳͳͷͰ ͱΓ͋͑ͣ ࡞ۀΛਐΊΔ
9. prefecture, station, area ໰୊ͳ͘ϩʔυऴྃ
10. category categories.csv ΧςΰϦϚελ σʔλ ఆٛ • id ΧςΰϦID • name ΧςΰϦ໊ • name_kana ΧςΰϦ໊ͻΒ͕ͳ • parent1, parent2 ਌ΧςΰϦID • similar ྨࣅΧςΰϦ໊ parent1, parent2ʹσʔλ͕͸ ͍ͬͯͨΓɺ0͕ઃఆ͞Ε͍ͯ ͨΓɺnullͱ0ͷ۠ผ͕ͳ͍ Load csvʹwhen then else endͷ۟Λૠೖͯ͠ɺload৚݅ Λهड़͢Δ͜ͱ͕Ͱ͖ͳ͍
11. LOAD CSVίϚϯυ Ͱ when then else end ͷهड़͕ૠೖͰ͖ͳ ͍ Ұ୴ɺcategories.csvͷશ ߦΛϩʔυ Category node࡞੒ޙmatch ͰΫΤϦͰ͖ͨ΋ͷ͚ͩ ਌ category - ࢠcategoryͷ relationshipΛઃఆ
12. MATCHͰnode not foundͩ ͱnull͕ฦ͞ΕΔ null nodeͱrelationshipΛੜ ੒͓͠Α͏ͱ͢ΔͱɺΤϥʔ ͱͳΔ nullͷnode͕ฦ͖ͬͯͨͱ͖ ͸ɺignoreͯ͠ɺॲཧΛܧଓ ͯ͠ཉ͍͠ FOREACHΛ࢖༻͠ɺWHEN node IS NULLͷॲཧΛهड़ ͜Ε͸known trickͰ͋ΔΑ͏ͩ http:// www.markhneedham.com/ blog/2014/08/22/neo4j- load-csv-handling- empty-columns/
13. restaurant categories.csv ͱಉ༷ʹࢀর ઌΦϒδΣΫτID͕0Ͱ͋ͬ ͨΓɺnullͰ͋ͬͨΓɺ౷Ұ ͞Ε͍ͯͳ͍ Category.csvΛϩʔυͨ͠ͱ͖ͱಉ͡ ํࣜͰϩʔυ͢Δ 1. Ұ୴શߦΛϩʔυ͠restaurant nodeΛ࡞੒ 2. վΊͯɺreferenceઌ node͕ଘ ࡏ͢Δ΋ͷ͚ͩɺrelationshipΛ ੜ੒
14. rating ratings.csv ޱίϛσʔλ • id ޱίϛID • restaurant_id ର৅͓ళID • user_id ϢʔβID • total ૯߹ධՁ(0-5) • food ྉཧධՁ(0-5) • service αʔϏεධՁ(0-5) • atmosphere งғؾධՁ(0-5) • cost_performance ίετύϑΥʔϚϯεධՁ (0-5) • title ޱίϛίϝϯτλΠτϧ • body ޱίϛίϝϯτ • purpose ར༻໨త • created_on ౤ߘ೔࣌ Rating id͕ॏෳ͍ͯ͠ΔՕॴ ͕͋Δ
15. Rating id͕ॏෳͯ͠ ͍Δ͕ɺ্ॻ͖ॲཧ ͍ͨ͠ʂ CREATE nodeͰͳ͘ MERGE nodeΛ࢖͏
16. CREATE (rating:Rating { id: line.id, total: line.total, title: line.title, comment: line.body }) ͸௨Δͷʹ MERGE (rating:Rating { id: line.id, total: line.total, title: line.title, comment: line.body }) ͸NGͰ͋Δ ʲݪҼʳ http://stackoverflow.com/ questions/35381968/cypher-node- already-exists-issue-with-merge MERGE͸ࢦఆnode͕ɺͳ͚Ε͹ɺ࡞੒͢Δ Id͕ಉ͡ɺ͔ͭɺpropertyͷҧ͏nodeͷmerge Λࢦఆ͍ͯ͠ΔͷͰɺnode࡞੒ΛτϥΠ͢Δ id unique constraintʹώοτ͠ɺΤϥʔͱͳΔ ʲରࡦʳ هड़ΛҎԼͷΑ͏ʹมߋ͢Δ MERGE (rating:Rating {id: line.id}) SET rating.total = line.total, rating.title = line.title, rating.comment = line.body
17. CREATEͳΒcsvͷΧϥ Ϝʹnull͕͸͍͍ͬͯͯ ΋ɺ֘౰propertyʹnull Λૠೖͯ͘͠Εͨͷʹ MERGEίϚϯυͰ͸Τ ϥʔͱͳΔ Categoryͷϩʔυͷͱ͖ʹ ࢖༻ͨ͠ FOREACHͷτ ϦοΫΛ࢖༻͠ nullΦϒ δΣΫτ൑ఆΛ࣮ࢪ͢Δ
18. ratingͱrestaurantͷ relationshipΛ࡞੒͢Δͱ ͖ɺҎԼͷΤϥʔ͕ͰΔ WARNING: Expected to find a node at restaurant but found nothing Some(null) (Failure when processing URL 'file://ratings.csv' on line 205833 (which is the last row in the file). Possibly the last row committed during import is line 204999. Note that this information might not be accurate.) Categoryͷϩʔυͷͱ͖ʹ ࢖༻ͨ͠ FOREACHͷτϦ οΫΛ࢖༻͠ nullΦϒδΣ Ϋτ൑ఆΛ࣮ࢪ͢Δ ೔࣌ͳͲͰηάϝϯτΛ͖ ΓɺҰ෦σʔλΛఏڙ͞Ε ͨ৔߹ͳͲͰ͸ɺࢀরઌ͕ ͳ͍Մೳੑ͕͋Δ
19. Finish loading Livedoor gourmet dataset !! load_livedoor_gourmet.cql ൃදऀϊʔτཝʹసه
20. Livedoor gourmet dataset ΛόΧͪΐΜͰϩʔυ͢Δʹ͸ 1. Download Livedoor gourmet data set 2. Extract data set 3. check neo4j config 
 dbms.directories.importύϥϝʔλͷ֬ೝ 4. Copy & past Load_livedoor_gourmet.cql 5. Load_livedoor_gourmet.cqlͷઌ಄෦෼ͷfilepathఆٛΛࣗ෼ͷ؀ڥʹ߹Θ ͤͯमਖ਼ 6. neo4j shell ίϚϯυ࣮ߦ
 $ neo4j-shell -file load_livedoor_gourmet.cql
21. Next try Query set Recommendation ͳͲΛߟ͑Δ