Now that we have our HANA instance up and running in the SAP HANA Cloud Platform trial platform as described in part 1 of this blog series, we can now start importing CSV like type of data.
This dataset is available in different format: CSV, JSON, XML, KML and MAP.
Step 1: Download and explore the file locally
Open the following URL and save the file locally: http://datasets.antwerpen.be/v4/gis/grondwaterstroming.csv
Open the file with a text editor like Notepad++ or Textpad or any text editor that you are used to. It only contains 123 rows.
"id";"objectid";"geometry";"shape";"diepte_min_mtaw";"diepte_max_mtaw";"gridcode";"shape_length";"shape_area"
"1";"22817";"{""type"":""Polygon"",""coordinates"":[[[4.3485873207025,51.346769755136],[4.3478973692569,51.346440719645],[4.347314661279,51.346163674265], [4.3468624645733,51.345941143175],[4.3462929983102,51.345670422578],[4.3449967290939,51.34505534943],[4.3439950525301,51.344583140302],[4.3437740104511,51.344478188117],[4.3434659797483,51.344331225892],[4.3429218895802,51.344074533317],[4.34288534622,51.344057673192],[4.3409137263619,51.343117163245],[4.3403091531947,51.342826173675],[4.3390567402791,51.342233658706],[4.3328651550485,51.33922930772],[4.331886599349,51.338758269204],[4.3311485526292,51.338404419669],[4.330086644905,51.33790188229],[4.3293988266884,51.337573177119],[4.328771087475,51.3372736987],[4.3272616188114,51.336637146853],[4.326651121465,51.336389487602],[4.3264888590539,51.336315846364],[4.3263967478269,51.336274801953],[4.3263400946058,51.33619950784],[4.326180091714,51.335864649775],[4.3261597950016,51.335761703446],[4.3266226585268,51.335868319593],[4.3270050610938,51.335953694495],[4.3273957730855,51.336038936191],[4.32759124408,51.336073687632],[4.328288007978,51.336119150872],[4.3285004665499,51.335954202951],[4.3289469355724,51.335584442431],[4.3297332822139,51.334937988123],[4.331938082533,51.335103690544],[4.3334930195943,51.335202599348],[4.3348311758461,51.335296097318],[4.3357146784906,51.335365575973],[4.3358590121613,51.33544551828],[4.3371420013413,51.335573524889],[4.3385397653228,51.335712205965],[4.3392999786683,51.335789635393],[4.3394574359333,51.335680550615],[4.3397974422046,51.335465064817],[4.340791892702,51.33483459416],[4.3410679772104,51.334672316669],[4.3415907689501,51.334323863148],[4.3418414929863,51.334164247584],[4.3420541077191,51.334028629994],[4.342249447717,51.333890204008],[4.3424026273298,51.333786453157],[4.3426405789147,51.333621485258],[4.342972054204,51.33339278501],[4.3432609134663,51.333190481572],[4.3438771876297,51.332764938422],[4.3442979127241,51.332469543799],[4.3454452057513,51.331671384125],[4.3465415697138,51.330905228029],[4.3473022037807,51.330375773714],[4.3473405482524,51.330354712588],[4.347574992935,51.329928803729],[4.3477466201118,51.329609202486],[4.3479276784484,51.329271617588],[4.3481372006834,51.328893456378],[4.3483373195894,51.328522456603],[4.3485432639002,51.328141903156],[4.3487451551991,51.327771046674],[4.3488613576963,51.327565878624],[4.3488880659212,51.327471652806],[4.3490101755812,51.327061172735],[4.3491188249319,51.326690300427],[4.3492256645973,51.326320398377],[4.3492674139323,51.326170143619],[4.3492822050077,51.326104448175],[4.3488413130698,51.326413448761],[4.3454583087765,51.327564439105],[4.33870870381,51.329080050325],[4.3197710284714,51.329520711012],[4.315591971097,51.330327213735],[4.3094752307029,51.333745773119],[4.3058687602067,51.338716106648],[4.3036718652719,51.343158643902],[4.2978329511419,51.35120174591],[4.2949066636125,51.35714279553],[4.2921285130391,51.365206828188],[4.2888072942056,51.366814517309],[4.2818242091877,51.369352852897],[4.2777400030282,51.372461008749],[4.2762063877381,51.375555926289],[4.3279056730946,51.37558468686],[4.3284916501884,51.3743992826],[4.3325572590312,51.369722140851],[4.3311804387299,51.368369840803],[4.3260762008003,51.367624679135],[4.323509919768,51.366187670841],[4.3220757486114,51.36453314703],[4.3197169172929,51.360409362886],[4.319544615571,51.358579341762],[4.3213585707178,51.354909704834],[4.3248051867653,51.350214989784],[4.3283502486996,51.34901025715],[4.3331034485997,51.347943919169],[4.3464405367933,51.347386342772],[4.3485873207025,51.346769755136]]]}";"";"2";"4";"2";"21448.786259186";"12187823.977738"
"4";"22818";"{""type"":""Polygon"",""coordinates"":[[[4.3654619939525,51.354810747631],[4.3640409482971,51.356188478285],[4.3634128505739,51.357430346935],[4.3635748822165,51.357411937903],[4.3637193395027,51.357413911619],[4.3639270482673,51.357393696305],[4.3641145577698,51.357387384207],[4.3641997968326,51.357420102891],[4.3642441925927,51.357483720537],[4.3643298794717,51.357484846745],[4.3644207237189,51.357437384575],[4.3645606881384,51.357436265298],[4.3647284774256,51.357375467121],[4.3648462358112,51.357348219117],[4.3649274294383,51.357346540553],[4.3649949407966,51.357370270318],[4.3651122519885,51.357374767228],[4.3652609584006,51.357376604355],[4.3653782688091,51.357395418694],[4.365496704718,51.357310881313],[4.3655915937937,51.357297823934],[4.3657044118086,51.357299372141],[4.3658062491747,51.357277605472],[4.3659096493009,51.357296284204],[4.3660404109148,51.357309345888],[4.3661891305792,51.357334343617],[4.3662884975973,51.357341229856],[4.3665729050578,51.357324947718],[4.3666987420707,51.357372414218],[4.3667900297115,51.357287587619],[4.3668308563108,51.357279588835],[4.3668990452383,51.357223127658],[4.3670446091818,51.357144772649],[4.3671534084717,51.357091682116],[4.3672850609737,51.357039023053],[4.3673759155645,51.356999980254],[4.3675073521592,51.356953082172],[4.3681223837244,51.356100492855],[4.366934744751,51.355521335176],[4.3655071620833,51.35483247234],[4.3654619939525,51.354810747631]]]}";"";"8";"10";"5";"987.57980133301";"57314.693996644"
This first row is the header with the column names and looking at the data we can deduct that the data types are:
This dataset is available in different format: CSV, JSON, XML, KML and MAP.
Step 1: Download and explore the file locally
Open the following URL and save the file locally: http://datasets.antwerpen.be/v4/gis/grondwaterstroming.csv
Open the file with a text editor like Notepad++ or Textpad or any text editor that you are used to. It only contains 123 rows.
"id";"objectid";"geometry";"shape";"diepte_min_mtaw";"diepte_max_mtaw";"gridcode";"shape_length";"shape_area"
"1";"22817";"{""type"":""Polygon"",""coordinates"":[[[4.3485873207025,51.346769755136],[4.3478973692569,51.346440719645],[4.347314661279,51.346163674265], [4.3468624645733,51.345941143175],[4.3462929983102,51.345670422578],[4.3449967290939,51.34505534943],[4.3439950525301,51.344583140302],[4.3437740104511,51.344478188117],[4.3434659797483,51.344331225892],[4.3429218895802,51.344074533317],[4.34288534622,51.344057673192],[4.3409137263619,51.343117163245],[4.3403091531947,51.342826173675],[4.3390567402791,51.342233658706],[4.3328651550485,51.33922930772],[4.331886599349,51.338758269204],[4.3311485526292,51.338404419669],[4.330086644905,51.33790188229],[4.3293988266884,51.337573177119],[4.328771087475,51.3372736987],[4.3272616188114,51.336637146853],[4.326651121465,51.336389487602],[4.3264888590539,51.336315846364],[4.3263967478269,51.336274801953],[4.3263400946058,51.33619950784],[4.326180091714,51.335864649775],[4.3261597950016,51.335761703446],[4.3266226585268,51.335868319593],[4.3270050610938,51.335953694495],[4.3273957730855,51.336038936191],[4.32759124408,51.336073687632],[4.328288007978,51.336119150872],[4.3285004665499,51.335954202951],[4.3289469355724,51.335584442431],[4.3297332822139,51.334937988123],[4.331938082533,51.335103690544],[4.3334930195943,51.335202599348],[4.3348311758461,51.335296097318],[4.3357146784906,51.335365575973],[4.3358590121613,51.33544551828],[4.3371420013413,51.335573524889],[4.3385397653228,51.335712205965],[4.3392999786683,51.335789635393],[4.3394574359333,51.335680550615],[4.3397974422046,51.335465064817],[4.340791892702,51.33483459416],[4.3410679772104,51.334672316669],[4.3415907689501,51.334323863148],[4.3418414929863,51.334164247584],[4.3420541077191,51.334028629994],[4.342249447717,51.333890204008],[4.3424026273298,51.333786453157],[4.3426405789147,51.333621485258],[4.342972054204,51.33339278501],[4.3432609134663,51.333190481572],[4.3438771876297,51.332764938422],[4.3442979127241,51.332469543799],[4.3454452057513,51.331671384125],[4.3465415697138,51.330905228029],[4.3473022037807,51.330375773714],[4.3473405482524,51.330354712588],[4.347574992935,51.329928803729],[4.3477466201118,51.329609202486],[4.3479276784484,51.329271617588],[4.3481372006834,51.328893456378],[4.3483373195894,51.328522456603],[4.3485432639002,51.328141903156],[4.3487451551991,51.327771046674],[4.3488613576963,51.327565878624],[4.3488880659212,51.327471652806],[4.3490101755812,51.327061172735],[4.3491188249319,51.326690300427],[4.3492256645973,51.326320398377],[4.3492674139323,51.326170143619],[4.3492822050077,51.326104448175],[4.3488413130698,51.326413448761],[4.3454583087765,51.327564439105],[4.33870870381,51.329080050325],[4.3197710284714,51.329520711012],[4.315591971097,51.330327213735],[4.3094752307029,51.333745773119],[4.3058687602067,51.338716106648],[4.3036718652719,51.343158643902],[4.2978329511419,51.35120174591],[4.2949066636125,51.35714279553],[4.2921285130391,51.365206828188],[4.2888072942056,51.366814517309],[4.2818242091877,51.369352852897],[4.2777400030282,51.372461008749],[4.2762063877381,51.375555926289],[4.3279056730946,51.37558468686],[4.3284916501884,51.3743992826],[4.3325572590312,51.369722140851],[4.3311804387299,51.368369840803],[4.3260762008003,51.367624679135],[4.323509919768,51.366187670841],[4.3220757486114,51.36453314703],[4.3197169172929,51.360409362886],[4.319544615571,51.358579341762],[4.3213585707178,51.354909704834],[4.3248051867653,51.350214989784],[4.3283502486996,51.34901025715],[4.3331034485997,51.347943919169],[4.3464405367933,51.347386342772],[4.3485873207025,51.346769755136]]]}";"";"2";"4";"2";"21448.786259186";"12187823.977738"
"4";"22818";"{""type"":""Polygon"",""coordinates"":[[[4.3654619939525,51.354810747631],[4.3640409482971,51.356188478285],[4.3634128505739,51.357430346935],[4.3635748822165,51.357411937903],[4.3637193395027,51.357413911619],[4.3639270482673,51.357393696305],[4.3641145577698,51.357387384207],[4.3641997968326,51.357420102891],[4.3642441925927,51.357483720537],[4.3643298794717,51.357484846745],[4.3644207237189,51.357437384575],[4.3645606881384,51.357436265298],[4.3647284774256,51.357375467121],[4.3648462358112,51.357348219117],[4.3649274294383,51.357346540553],[4.3649949407966,51.357370270318],[4.3651122519885,51.357374767228],[4.3652609584006,51.357376604355],[4.3653782688091,51.357395418694],[4.365496704718,51.357310881313],[4.3655915937937,51.357297823934],[4.3657044118086,51.357299372141],[4.3658062491747,51.357277605472],[4.3659096493009,51.357296284204],[4.3660404109148,51.357309345888],[4.3661891305792,51.357334343617],[4.3662884975973,51.357341229856],[4.3665729050578,51.357324947718],[4.3666987420707,51.357372414218],[4.3667900297115,51.357287587619],[4.3668308563108,51.357279588835],[4.3668990452383,51.357223127658],[4.3670446091818,51.357144772649],[4.3671534084717,51.357091682116],[4.3672850609737,51.357039023053],[4.3673759155645,51.356999980254],[4.3675073521592,51.356953082172],[4.3681223837244,51.356100492855],[4.366934744751,51.355521335176],[4.3655071620833,51.35483247234],[4.3654619939525,51.354810747631]]]}";"";"8";"10";"5";"987.57980133301";"57314.693996644"
This first row is the header with the column names and looking at the data we can deduct that the data types are:
| id | integer |
| objectid | integer |
| geometry | long text |
| shape | empty so string |
| diepte_min_mtaw | integer |
| diepte_max_mtaw | integer |
| gridcode | integer |
| shape_length | float |
| shape_area | float |
We can also notice that the separator is the “semi colon” and the field values are enclosed by double quotes.
Step 2: Import the local file using Eclipse
First make sure you are using the SAP HANA Administration Console perspective.
Now, using the “File > Import…” menu, type in “Data from Local File” in the search box, then click on “Next“:
Select your “Target System” and click “Next“.
Then use the “Browse” button to select your file, change the “Field Delimiter” to “Semi Colon“, check the “Header exists” and input 1 in the filed, check “Import all data“, the pick your schema and table name.
Click on “Next“.
On the screen you will have the ability to adjust the “Table Settings and Data Mapping” settings where you will have to select “id” as “Key“:
You might to adjust the data type here, as they will be guess from the first hundreds of rows.
Click on “Finish“.
Congratulations, your data has been uploaded. Hit “F5” to refresh the tree:
Now that you know how to upload a CSV file into HANA, let’s get it a bit more … sophisticated.
You probably noticed that the “geometry” filed was imported a Blob, but looked very much like a geoJSON piece of information which we should store into a ST_GEOMETRY column type and use that in the Spatial engine.
However, geoJSON is not supported by HANA out of the box, so we will see in part 3 how we can convert geoJSON into the “Well-Known Text” format: Port of Antwerp from the Opendata challenge perspective – part 3.
No comments:
Post a Comment