Playing with LucidDB: namespace and users

One of the challenges (and part of the fun) of using a relatively new open source project, is useing the software despite the lack of complete documentation. The nice thing about open source is that if you have a question, there usually is a discussion board or list-serve where you can ask the creator directly. In the spirit of openness, I am posting some of little pointers about using the column based open source database LucidDB especially comparing to to MySQL. There is nothing new here, but if you come to LucidDB from MySQL there are a few (minor) gotchas that is would be nice to avoid. So here is a post about the LucidDB namespace and users. (BTW, LucidDB's approach to namespace and users should be applicable to many databases)

LucidDB is a column-store database designed for data warehousing and business intelligence. It is open source, and has a growing community, including a new corporate sponsor: Dynamo BI. Column-store databases are faster than row-store databases when executing the kinds of queries that are common in data warehouses. There are a number of speed comparisons of LucidDB on the web, and it always does rather well. Additionally, Lucid as a number of interesting features such as the ability to connect to other databases (and even Hadoop!) and run queries on the remote database.

Navigation and basic interaction:

Installing LucidDB is pretty easy, and there are directions on the LudicDB site, so let's start with some basic navigation, and compare it to MySQL.

In MySQL, the highest level of a namespace is the "database." So a single project might have a single database, but there might be other databases running on the same MySQL instance. Any table can be referred to by its qualified name as in:

select * from databaseName.tableName

When you refer to a table without a database name, MySQL will assume you are referring to the presently used database. This is equivalent to the above query.

use databaseName;
select * from tableName;

The highest level of the LucidDB namespace is "catalog" and under catalog is "schema." (I think this is closer to the SQL standard than MySQL.) Here is an example of a LucidDB query with a qualified name:

select * from catalogName.schamaName.tableName

Like MySQL, LucidDB assumes the present catalog and schema when you refer to a table without a catalog and schema. You can change catalog or schema in LucidDB by using the "SET" command (Note the single quotes around the catalog and schema names!):

set catalog 'catalogName';
set schema 'schemaName';
select * from tableName;

If you get lost, MySQL has a function to tell you what database you are in:

select database();

LucidDB does not have an equivalent function (though you could write one if you want). Instead, you must query one of the special systems views. These views expose information about the state of the system. For the current catalog and current schema, you can query the user_session_parameters view to get both:

select * from LOCALDB.sys_root.user_session_parameters where param_name in ('schemaName', 'catalogName');

You can use the same system view to get a lot of information about your current session as well.

In Mysql, the SHOW command is useful to see structure of the database (and a list of available databases). For example:

SHOW databases;
SHOW TABLES;
SHOW VIEWS;

(PostgresQL has its own commands: \dt to show tables). To get similar information from LucidDB you must again go to the systems views. The view contain all the information about tables, views, triggers, databases, etc. Refer to this link for details about all the information you can see, but here are some examples:

SELECT * FROM sys_root.DBA_SCHEMAS;
SELECT * FROM sys_root.DBA_TABLES;
SELECT * FROM sys_root.DBA_VIEWS;

Creating users

When you install LucidDB, there is a default administrative user called "sa" with no password. You can log into LucidDB and create a new user, and set the user's default schema:

create or replace user "sa" identified by 'aGoodPassword';

Or create a new user:

create user "userName" identified by 'aBetterPassword' default schema test_schema;

Lastly, there are a two useful systems views that have information about users, DBA_USERS and DBA_ROLES. However, they are only available in the (yet) unreleased version 0.9.4.

As a side note, it appears that you can set up LudidDB to use JAAS, but the documentation is unclear (to me), and I have not tried it.