👍

Create AMI on AWS

https://aws.amazon.com/marketplace/pp/B07KCNF1JL

When you instantiate a BlobCity DB AMI from the AWS Marketplace, a default instance of BlobCity is already started and running on the instance. You will need to ssh into your AMI and retrieve the automatically generated password to authenticate yourself into your running instance of BlobCity DB.

SSH into AMI

The username of the default user on the AMI is ubuntu. To SSH into the AMI, you will need your SSH key which was selected during AMI creation.

ssh -i <your_key_file> ubuntu@<instance public dns / ip>

Getting DB Access Credentials

Your BlobCity instance starts with a user called root already created. The password for this user is automatically generated at first boot and written to a text file located at /mnt/data/root-pass.txt.

You may retain this password for accessing your database, but it is highly recommended you change your password upon first login

Accessing DB CLI

The command line interface is the preferred choice for managing your database instance. You can connect to the CLI by simply typing blobcity command on the terminal window from within your AMI. You will have to ssh into your AMI first. The CLI interface will ask for a username and password authentication before any CLI operation is permitted.

ubuntu@ip-172-31-35-48:~$ blobcity
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
username>root
password>xxxxx
You are now inside the BlobCity DB console
Type 'help' for assistance and 'exit' to quit
blobcity>

Creating a Datastore & Collection

You must create a datastore and then create collections within the datastore to use the database. The CLI interface can be used to create both the datastore and collections within the datastore.

blobcity>create-ds test
Datastore successfully created
blobcity>create-collection test.my_collection
Collection successfully created
blobcity>create-collection test.my_collection2
Collection successfully created

You may now insert data into any of the created collections and fire SQL queries on data stored in the collections.

Inserting Data

Data can be inserted using the REST API, or data files can be loaded onto the server and then an import command maybe fired using the CLI.

The database can access data files located within the folder /mnt/data/{ds}/ftp. You will need to use scp, rsync or any other machoism you are comfortable with to load your data files into this folder on the server.

The {ds} attributed must be substituted with the name of your datastore.

blobcity>import-csv test.my_collection /my_file.csv
Done in 37 (ms)

The above example imports the CSV file my_file.csv into collection my_collection. The first row of the CSV is assumed to contain column names. my_file.csv in this case is must be placed at /mnt/data/test/ftp/my_file.csv on the server for the import operation to succeed.

Running SQL Queries

Once data is loaded, you may fire an SQL query on the collection to select your data. You can do this using the CLI or the REST interface.

blobcity>sql test: select * from `test`.`my_collection` where `col1` = 2

{
  "username" : "root",
  "password" : "xxxxx",
  "ds" : "test",
  "q" : "sql",
  "p" : {
    "sql" : "select * from `test`.`my_collection` where `col1` = 2”
  }
}

Setup an EBS volume

While not necessary, it is highly recommend you setup an EBS volume to store your data. The database by default stores data onto the boot volume, which is susceptible to data loss on terminating the AMI or if your AMI crashes for any reason.

An EBS volume can be retained on terminating an AMI and the same volume can be mounted on another AMI you create, thereby allowing you flexibility and better protection against total data loss.

The EBS volume must be XFS formatted for optimal performance, specially if you primarily plan to use the on-disk storage engine. XFS or EXT4 maybe used with almost equal performance if primary data storage is in-memory.

Mount the EBS volume at /mnt/data. This will override any existing data you have placed inside the folder. If you desire to mount the volume at another location say /mnt/my_vol, then the database can be configured to write to this volume by changing the path in blobcity.sh file located at /home/ubuntu/blobcity-db-1.7.1-alpha/bin/blobcity.sh. The line

export BLOBCITY_DATA=/mnt/data/

specifies the default path which can be changed to

export BLOBCITY_DATA=/mnt/my_vol