- SAP Managed Tags:
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.
We will be using content from the Antwerp Open data site: http://opendata.antwerpen.be/
Let's use the following dataset: Groundwater : the groundwater is high in the south of Antwerp and descends in a northerly direction.Under natural conditions the groundwater flows into the Scheldt and waterways, such as the Grand Sham and the Ostrich Beek.
This dataset is available in different format: CSV, JSON, XML, KML and MAP.
We will focus here on the CSV format which can be downloaded from here: http://datasets.antwerpen.be/v4/gis/grondwaterstroming.csv
Note that some the CSV files does only include a part (up to 20k records). This is why we will look at the JSON format in part 3 as it implements a pagination mechanism and allows to get all records.
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.
This first row is the header with the column names and looking at the data we can deduct that the data types are:
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.
And off course if you have any feedback or idea to enhance this content, feel free to add your comment or simply share it if you liked it!
We will be using content from the Antwerp Open data site: http://opendata.antwerpen.be/
Let's use the following dataset: Groundwater : the groundwater is high in the south of Antwerp and descends in a northerly direction.Under natural conditions the groundwater flows into the Scheldt and waterways, such as the Grand Sham and the Ostrich Beek.
This dataset is available in different format: CSV, JSON, XML, KML and MAP.
We will focus here on the CSV format which can be downloaded from here: http://datasets.antwerpen.be/v4/gis/grondwaterstroming.csv
Note that some the CSV files does only include a part (up to 20k records). This is why we will look at the JSON format in part 3 as it implements a pagination mechanism and allows to get all records.
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.
And off course if you have any feedback or idea to enhance this content, feel free to add your comment or simply share it if you liked it!
