#data #quickstart #pe_utils #smb ## Overview Datasette is a tool for exploring and publishing data. It is especially useful for those who need to quickly prepare and distribute static data of various sorts (e.g. public data, diligence data, financial data, etc.). Datasette has a large ecosystem of plugins. This quickstart guide outlines just those which will likely be useful to the Shadow Lake team on a recurring basis. This quickstart guide provides the steps for setting up a sample set of public data on a cloud hosted application. In this case, the source data is a set of three sanctions csv files which are combined in a SQLite database. Naturally, one could start with a SQLite databse, not a set of csv files, these steps could be repeated for any set of data. ## References [Datasette](https://datasette.io/) [sqlite-utils](https://github.com/simonw/sqlite-utils) ## Requirements to get started - [ ] Python - [ ] PIP - [ ] Heroku ## Setup Create and navigate to a directory ``` mkdir sanctions ``` ``` cd sanctions ``` Create and activate a virtual environment ``` virtualenv venv ``` ``` source venv/bin/activate ``` Pip install the necessary libraries ``` pip install sqlite-utils ``` ``` pip install datasette ``` ``` pip install datasette-copyable ``` ``` datasette install datasette-search-all ``` ``` datasette install datasette-auth-passwords ``` ``` datasette install datasette-configure-fts ``` Download the (sample) data into the directory Make a sqlite file from the csv files (note the hard-coded file name: 'sanctions.db') ```bash for file in *.csv; do table=$(basename "$file" .csv) sqlite-utils insert sanctions.db "$table" "$file" --csv done ``` Create a metadata.json file in the directory and insert the below into it (note the hard-coded values). This metadata.json file is where we will configure the password hash for our login and also wherein we will specify that the app is private by default. ```json { "plugins": { "datasette-auth-passwords": { "someusername_password_hash": { "$env": "PASSWORD_HASH_1" } } }, "title": "Title Goes Here", "allow": { "id": "root" } } ``` Configure the full text search fields by running the datasette instance as the root user and then adding the following path to the url: /-/configure-fts. (You can also use sqlite-utils to configure columns in the terminal). This page will bring you to a checklist wherein you can check each of the columns you want to make searchable through full text search. (Note: when you run the below command, you must click on the ip address with the cookie attached to access the instance as a root user. Do not click on the ip address without the cooke attached or you will start the instance as a regular user.) ``` datasette sanctions.db --root ``` While the instance is open, add the following path to the url to find the password hash tool: /-/password-tool. Type in the password you want to use for the user and copy the resulting hash. In this case, because this is a demo of public data, we use 'sanctions' as the password, which results in the below hash. (Note: this is for demo purposes only. In real life never add passwords or hashes to a repo) ``` pbkdf2_sha256$480000$3b2daa53b55c1f975351a8e41ddd2db0$d1yGLJjK5Y7wFpQb6URAEGVO3ZRm1f85hsTyJyBv6Ac= ``` The password hash can now be specified in an environment variable when you run Datasette. You can do that like so (be sure to use single quotes). This will launch your datasette instance. You will now be able to log in to your instance using the form at /-/login with someusername as the username and the password that you used to create your hash as the password. ``` PASSWORD_HASH_1='pbkdf2_sha256$...' \ datasette --metadata metadata.json --root ``` The app is now ready to deploy. To do so, log into heroku and use the below command (note the hard-coded values): ``` datasette publish heroku sanctions.db \ -n datasette-sanctions-demo \ --install datasette-auth-passwords \ --install datasette-configure-fts \ --install datasette-search-all \ --install datasette-copyable \ --plugin-secret datasette-auth-passwords root_password_hash 'pbkdf2_sha256$480000$3b2daa53b55c1f975351a8e41ddd2db0$d1yGLJjK5Y7wFpQb6URAEGVO3ZRm1f85hsTyJyBv6Ac=' \ --metadata metadata.json ``` You can now visit your datasette online at the link provided by heroku. In this case the link is [https://datasette-sanctions-demo-a72a2788ef07.herokuapp.com/](https://datasette-sanctions-demo-a72a2788ef07.herokuapp.com/). Login by adding '/-/login' to the url and using 'root' and 'sanctions'. This is just the beginning. There are many plugins one can add here, to include geomaps, dashboards, and codespaces. It is also worth pointing out that the full text search feature accepts any language, and even emoji. In this demo, trying passing in the following Arabic characters to view how quickly this works. ``` حاجی خيرالله و حاجی ستار صرافی ``` Finally, it is worth pointing out that password protecting apps is only necessary if you are planning on publishing sensitive data. If you are simply looking to work locally, or publish public data, there is no need to add a metadata.json file or configure passwords. You can skip that process altogether.