Message Boards Message Boards

0
|
7052 Views
|
6 Replies
|
1 Total Likes
View groups...
Share
Share this post:

How to load a RelationalDatabase stored in the WolframCloud?

I have an SQLite database with about 13 "tables" represented as a file and stored in the WolframCloud with public permissions located at the following URL:

 chinook="https://www.wolframcloud.com/obj/schandler/Public/chinook.sqlite";

I want to provide access to it and let people import whatever tables they want from it. Now, when that same database was hosted on my hard drive (or, actually, on my private Dropbox account), it was simple. I just did the following:

 schema=RelationalDatabase[FindFile[path<>"chinook.sqlite"]]

And that worked. But I've tried various permutations (and embarrassingly more) of the following, none of which work.

 RelationalDatabase[chinook];
 RelationalDatabase[URL[chinook]];
 RelationalDatabase[CloudObject[chinook]];
 RelationalDatabase[CloudGet[CloudObject[chinook]]];
 RelationalDatabase[DatabaseReference[URL[chinook]]];

Can someone help?

I'd like to be able to let people access the whole Database via RelationalDatabase[something] or tables from the Database via RelationalDatabase[{table1,table2,...tablem},something]

And, yes, I could let people download the file from the public cloud object to their hard drive and write RelationalDatabase[FindFile[location]], but I REALLY want to save them that step. And, yes, I could make the file public on my Dropbox account, but I'd prefer for various reasons to host this on the WolframCloud.

Thanks! (I'm cross posting on mathematica.stackexchange.com due to urgency)
POSTED BY: Seth Chandler
6 Replies
Sort By:

There's been an interesting development on this issue. LouisB on mathematica.stackexchange.com suggested just running

 CloudSave[RelationalDatabase[FindFile["mylocalcopy.sqlite"]]

This yields a URL of the CloudObject. One can then give people that URL (or put the URL in a ResourceObject) and then have them run CloudGet[theURL]. LouisB demonstrated his claim with the e-commerce database that comes in ExampleData.

I replicated the LouisB approach on my machine and found (to my surprise) that something so obvious worked when I used his exact code. However, when I used my own database, it failed when I tried to CloudSave. I got a CloudObject: An unknown error occurred." This failure occurred even though the database worked splendidly on my own machine. There didn't appear to be anything different between the ecommerce database and mine, so I was mystified. Except that the ecommerce database had a .sqlite suffix and mine had a .db suffix. But surely that couldn't matter to the WolframCloud. But, evidently it does. The whole problem disappears if I change the suffix on my file from .db to .sqlite.

So, I don't know why the WolframCloud cares what the suffix is on my file. And I don't think it should care. But, for the moment it does. Developers take note. This seems really strange and un-Wolframy.
POSTED BY: Seth Chandler

I appreciate the effort to work on the inability to access a RelationalDatabase stored as an SQLite file on the Wolfram Cloud. As shown in the attached notebook, however, the fix suggested by Mr. Poeschko does not appear to work. Any revision to the code would be appreciated.

More fundamentally, though, I don't think RelationalDatabase is somehow being too generous in claiming to work on a remote SQLite database. Whether the file itself is hosted on my local machine, on Dropbox or on Wolfram Cloud should not matter. And, yes, it may be unorthodox to work on a local copy of a database, but for pedagogic purposes in teaching people how to use a Database WITHOUT having to go through the issues of authentication or learning how to spin up a SQL server is a big deal. That's the promise of RelationalDatabase and, in my opinion, if technically possible, it ought to be completely fulfilled. Thus, the suggestion to the developers.

Attachments:
Hosting SqLite o...nb
POSTED BY: Seth Chandler

I received an answer from Wolfram Research Technical Support. They say that it is not presently possible to access a file-based relational database (like SQLite) by specifying a URL. You have to get the underlying file at the URL, copy it to a local drive, and then treat it like a file. I have requested that the developers see if this limitation can be removed or, at a minimum, the documentation for the RelationalDatabase function make that limitation more clear.

POSTED BY: Seth Chandler

Arguably, RelationalDatabase shouldn't even give the impression that it can directly (efficiently & consistently) connect to a remote SQLite database (like it can with other databases). With other databases, you're not usually operating on a local copy. That's just my 2c, though.

One quite straight-forward "one-line" way to explicitly create the local copy yourself is via LocalCache:

RelationalDatabase[ExpandFileName[URL @@ LocalCache[CloudObject[chinook]]]]

(I've already made an internal suggestion to make this a little less verbose.)

If you prefer a fresh copy everytime, you could also use URLDownload (as opposed to users having to download the file manually).

Maybe you've already considered this, I just wanted to point it out for reference.
POSTED BY: Jan Poeschko

Yes, I have CloudConnectivity. I wish it were that simply resolved.
POSTED BY: Seth Chandler
Anonymous User
Anonymous User
Posted 5 years ago

Did you insure your connectivity to that Cloud to receive files is working?

You did first try putting a simple (named) expression or file on the Cloud, "2+3" and were able to get it back from the cloud, right?
POSTED BY: Anonymous User
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract