This self-contained tool enables incremental synchronization of your data from Google App Engine's Datastore to a local SQLite 3 database. It will automatically convert your model definitions into an SQL schema, and download the model data in parallel using the new remote_api.
Synchronization is currently unidirectional (i.e. Datastore to SQLite, not the other way around) due to a lack of need, and also at least one technical limitation.
Introduction
There are still many data processing tasks that aren't possible on App Engine, to which a traditional SQL database is ideally suited. These include driving a full text indexer, running reports that aggregate complex statistics in multiple dimensions from your data, or asking ad-hoc questions that your application's counters weren't designed to support.
This utility solves these problems, while enabling many more applications to integrate with your data with minimal time and effort. An SQL schema is automatically generated by introspecting the model classes defined in your application's source code, into which new and updated entities are automatically fetched with a single command.
The database may be used for running interactive queries using one of the many free GUI management tools, or even hooking up to Microsoft Excel via ODBC. Additionally, by configuring a cron job it is possible to mirror your entire Datastore to an external location, for consumption by external software (for example, full text indexing, automated reporting and analysis, etc.)
How This Works
The magic key
property is used to search for new immutable entities, while any Model
subclasses that define at least one DateTimeProperty
with auto_now=True
will be automatically re-fetched every time they are
modified. This means that you gain completely hands-free replication to an
effortlessly accessible SQL database simply by including a single
DateTimeProperty
in each of your mutable models.
Model → SQL Schema Mapping
All identifiers are mapped like so: ModelClassName
→ model_class_name
, and ModelClassName.propertyName
→ model_class_name_property_name
.
The current code generates the following SQL objects:
-
One
TABLE
for each Model subclass, containing a unique integer (id
) and string-encoded Datastore Key. -
One
TABLE
for each property of each Model, containing an integer (class_name_id
) and the actual value column (value
). -
One
VIEW
for each Model, defined as aSELECT
which joins together all the other tables into a single combined "SQL like" table. This is the most useful object.
This design was chosen to make it trivial to add and drop columns on large data sets, without having to wait for large ALTER TABLE
commands to run. It also makes it easier to define custom VIEW
s on just the subset of your data you need.
Type Mapping
SQL Type | Datastore Native Type |
---|---|
TEXT | Key, ByteString, IM, User, GeoPt, PhoneNumber, Email, etc. (all simple text fields) |
TIMESTAMP | datetime, date, time |
INTEGER | int, long, Rating |
REAL | float |
Example
The following model:
class Address(db.Model):
careOf = db.StringProperty()
line1 = db.StringProperty()
line2 = db.StringProperty()
lastUpdated = db.DateTimeProperty(auto_now=True)
Would be mapped to:
CREATE TABLE address_entity(id INTEGER PRIMARY KEY, ds_key TEXT NOT NULL);
CREATE TABLE address_care_of(address_entity_id INT NOT NULL, value TEXT);
CREATE TABLE address_line1(address_entity_id INT NOT NULL, value TEXT);
CREATE TABLE address_line2(address_entity_id INT NOT NULL, value TEXT);
CREATE TABLE address_last_updated(address_entity_id INT NOT NULL, value TIMESTAMP);
CREATE UNIQUE INDEX address_entity_ds_key_index
ON address_entity(ds_key);
CREATE UNIQUE INDEX address_care_of_address_entity_id_index
ON address_care_of(address_entity_id);
CREATE UNIQUE INDEX address_line1_address_entity_id_index
ON address_line1(address_entity_id);
CREATE UNIQUE INDEX address_line2_address_entity_id_index
ON address_line2(address_entity_id);
CREATE UNIQUE INDEX address_last_updated_address_entity_id_index
ON address_last_updated(address_entity_id);
CREATE VIEW address AS
SELECT
address_entity.id AS __id,
address_entity.ds_key AS __ds_key,
address_care_of.value AS care_of,
address_line1.value AS line1,
address_line2.value AS line2,
address_last_updated.value AS last_updated
FROM
address_entity
LEFT JOIN address_care_of ON (address_care_of.address_entity_id = address_entity.id)
LEFT JOIN address_line1 ON (address_line1.address_entity_id = address_entity.id)
LEFT JOIN address_line2 ON (address_line2.address_entity_id = address_entity.id)
LEFT JOIN address_last_updated ON (address_last_updated.address_entity_id = address_entity.id)
-- Example query:
SELECT COUNT(\*) FROM address WHERE line1 LIKE '1 Infinite Loop%'
Usage
Usage: dsync.py <command> [options]
Options:
-a <name> App Engine application name, e.g. "shell"
-e <addr> Developer's e-mail address (default: prompt)
-p <pw> Developer's password (default: prompt)
-r <path> remote_api path on server (default "/remote_api")
-L <path> Prepend extra path to module search path
-m <name> Load Model classes from module
-d <path> Local database path (default "./models.sqlite3")
-x <name> Exclude the given Model class
-N <count> Number of fetch worker threads (default: 5)
-C <count> Number of entities to fetch per request (default: 50)
-v Verbose/debug output
--batch Fail rather than prompt for a password if none is
provided on the command line.
--sdk-path=<path>
Path to App Engine SDK (default: search).
--trigger-cmd="<ModelGlob>:command"
Arrange for the given system command to be executed
after new or updated entities whose class matches
ModelGlob fetched (glob may contain * and ?).
--trigger-sql="<ModelGlob>:SQL STATEMENT"
Arrange for the given SQL statement to be executed
after new or updated entities whose class matches
ModelGlob fetched (glob may contain * and ?).
--help This message.
Commands:
sync-model
Synchronize the database schema to match the loaded Model
subclasses.
fetch
Start fetching data from Datastore to the database,
synchronizing its schema if necessary.
orphaned
List properties and tables that no longer have associated
definitions in the loaded Model classes.
prune
Remove properties and tables from local database that no
longer have associated definitions in the loaded Model
classes. Check "orphaned" output first to verify this
won't result in data loss!
Example:
Backup "myapp.appspot.com"'s datastore to $HOME/myapp.db
except for RemoteUrlCacheEntry:
dsync.py -L $HOME/src -m myapp.models -m myapp.counters \
-d $HOME/myapp.db -x RemoteUrlCacheEntry \
-a myapp -e me@gmail.com -p 1234 \
fetch
Known Issues
This code represents about 10 hours worth of work; it's still at a very early stage. Please see the issue tracker for a more definitive list of issues, but most importantly right now:
- With high worker thread counts, SQLite locks timeout.
- Not well tested.
- Only supports SQLite; PostgreSQL support would be great for very large data sets.
I will be productionizing the code in the coming months, but if you come across a major issue, please tell me about it.
Configuration
Local System
Before using the utility, you must have Python 2.5 and the Google App Engine SDK installed on your working machine, along with a copy of your site's source code. The source code is currently necessary for introspecting your application's Datastore schema.
The utility searches for the SDK in the default locations for Windows and OS X. If you have changed the install path for the SDK, you will need to specify it using a command line parameter.
After completing these steps, you must configure a remote_api
URI for your
site as shown below.
Application Changes
Simply add the following to app.yaml in your project directory:
handlers:
- url: /remote_api
script: $PYTHON_LIB/google/appengine/ext/remote_api/handler.py
login: admin
It is recommended that you use the URI shown above, however you can specify an
alternative using a command-line parameter if you desire. This enables the
remote_api
handler built into App Engine, which you can read more
about here.
Creating A Sync Script
It is recommended that you create a batch file or UNIX shell script to save the settings used for launching the utility. This helps reduce the potential for human error cropping up (for example, missing a library path).
The main tasks required for this are picking a stable location for your site's
source code, say, $HOME/src/my-site
(or %USERPROFILE%\my-site
for Windows
users), and figuring out which modules in your source code contain Datastore
model definitions. This will be easier if you use only a single file for
declaring models.
Here is an example sync script written in bash. It will prompt for me@gmail.com's password at startup, or you can hard-code it using the -p option.
#!/bin/bash
EMAIL=me@gmail.com
APPNAME=mysite # mysite.appspot.com
SOURCE_DIR=$HOME/src/my-site
DATABASE_PATH=$HOME/data/my-site.sqlite3
TRIG="Member:$HOME/bin/index-new-members.sh"
dsync.py \
-L $SOURCE_DIR \
-m my_site.models \ # Equivalent to $SOURCEDIR/my_site/models.py
-m my_site.counter \
-a $APPNAME \
-e $EMAIL \
-N 5 \ # Start 5 worker threads.
--trigger-cmd="$TRIG" \ # Trigger indexing when a new member joins.
fetch
Start Syncing
$ ./my-sync-script.sh
INFO:root:Server: my-site.appspot.com
INFO:root:Session done: 1 added, 0 updated.
INFO:root:Room done: 1 added, 0 updated.
INFO:root:Invitation done: 2 added, 0 updated.
INFO:root:Video done: 5 added, 0 updated.
INFO:root:Item done: 5 added, 0 updated.
INFO:root:Member done: 2 added, 0 updated.
INFO:root:grand total: 1.73s for 16 added, 0 updated in 13 models.
$
$ ./my-sync-script.sh
INFO:root:Server: my-site.appspot.com
INFO:root:grand total: 0.97s for 0 added, 0 updated in 13 models.
If all has gone well, you should now have a local SQLite copy of your Datastore!
Run Some Queries
$ sqlite3 $HOME/data/my-site.sqlite3
SQLite version 3.5.7
Enter ".help" for instructions
sqlite> .mode column
sqlite> SELECT COUNT(*) FROM session;
COUNT(*)
----------
1
sqlite> SELECT session_id, display_name, last_seen, last_address, expiry_time, first_seen FROM session;
session_id display_name last_seen last_address expiry_time first_seen
-------------------------------- ------------ ------------------- -------------- ----------- -------------------
0ed373e4a697d2fed5b7e6b00f462423 Anonymous 2009-02-24 15:02:45 80.127.152.220 2009-02-24 12:16:03