Guidance for querying db copied from oracle

Using some hacks to the “oracle2couchbase” app on github, I was able to import my set of Oracle tables into a Couchbase database.

I now need to explore running n1q1 queries on this db.

The Oracle db had 14 tables. One of the tables, after importing, resulted in a set of documents with keys like “blahblah_api_keys::058170AA231C3B45E0540010E00CE518”. When I look in the “Documents” list in the bucket, I don’t see an easy way to search for “substrings” in the key so I can see documents from other tables.

Now that I have these docs in the db, it’s not clear to me exactly how to query them with n1q1. I didn’t have any luck with specifying a “from” value of “blahblah_api_keys”. I’m not sure what to do with this.

Hmm, the “from” is supposed to be a bucket, right? Ok, there’s more I have to figure out here. If I wanted to be able to query all of the docs whose key begins with “blahblah_api_keys”, do I have to create an index, or is this something you’d do with a view?

For development time work, create a ‘primary index’ as covered in the docs, and then from the query tab you can query FROM that bucket, matching the substring with LIKE or using the other operators covered in the documentation.

For production time, you’ll likely want to create indexes that match your queries.

I’m still absorbing docs. I don’t see a way to create a primary index from the gui. Is this something that can only be done from cbq (or the sdk, I supose)?

Ah, it’s just a query statement. It’s running now, I’ll see if that helps.

You can issue SQL statements using N1QL. In additions to documentation checkout

When I first did this I translated the data from all 14 of the original Oracle tables to the single bucket. Under what circumstances would I consider having a 1-1 correspondence between tables and buckets? I know that CB is schemaless, so there’s no technical reason I can’t put documents with different “schemas” in the same bucket.

Check this out.