CODEWITHSUNDEEP
pythongoogle-bigquerysqlalchemy
user1403505
user1403505

Reputation: 1005

CRUD web application with BigQuery?

I have a BigQuery table about 200 rows, I need to insert,delete and update values in this through a web interface(the table cannot be migrated to any other relational or non-relational database).

The web application will be deployed in google-cloud on app-engine and the user who acts as admin and owner privileges on BigQuery will be able to create and delete records and the other users with view permissions on the dataset in BigQuery will be able to view records only.

I am planning to use the scripting language as python, 
server(django or flask or any other)-> not sure which one is better

The web application should be displayed as a data-grid like appearance with buttons create, delete or view visiblility according to their roles.

I have not done anything like this in Python, BigQuery and Django. I am already familiar with calling BigQuery from python-client but to call in a web interface and in a transactional way, I am totally new. I am seeing examples only related to django with their inbuilt model and not with big-query.

Is this possible to implement?

Upvotes: 2

Views: 2755

Answers (2)

user1403505
user1403505

Reputation: 1005

I was able to achieve all of "C R U D" on Bigquery with the help of SQLAlchemy, though I had make a lot of concessions like if i use sqlalchemy class i needed to use a false primary key as Bigquery does not use any primary key and for storing sessions i needed to use file based session On Django for updates and create sqlalchemy does not allow without primary key, so i used raw sql part of SqlAlchemy. Thanks to the @mhawke who provided the hint for me to carry out this exericse

Upvotes: 1

Brian Burton
Brian Burton

Reputation: 3842

No, at most you could achieve the "R" of "CRUD." BigQuery isn't a transactional database, it's for querying vast amounts of data and preparing the results as an immutable view.

It doesn't provide a method to modify the source data directly and even if you did you'd need to run the query again. Also important to note are that queries are asynchronous and require much longer to perform than traditional databases.

The only reasonable solution would be to export the table data to GCS and then import it into a normal database for querying. Alternatively if you can't use another database and since you said there are only 1,000 rows you could perform your CRUD actions directly on that exported CSV.

Upvotes: 0

Related Questions