1

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. Can anyone please help me and clarify whether this is possible to implement and how?

davidism
  • 121,510
  • 29
  • 395
  • 339
user1403505
  • 895
  • 2
  • 19
  • 42
  • 1
    Not sure. Check this question https://stackoverflow.com/questions/46435185/using-google-bigquery-as-a-backend-for-django – mhawke Jan 25 '21 at 11:54
  • thanks, saw that before posting it but there is no solution given there – user1403505 Jan 25 '21 at 11:58
  • 1
    One of the answers mentions that bigquery can be used through SQL Alchemy. It might be worth a look if you haven't already done so. – mhawke Jan 25 '21 at 12:01
  • This is certainly possible with a starter project like [this](https://github.com/winwiz1/crisp-bigquery). You will need to extend it and add authentication/authorization using some popular NodeJS library like PassportJS. Then implement other CRUD methods in addition to the existing read/query functionality. – winwiz1 Jan 27 '21 at 11:48
  • @mhawke Thanks for providing this important piece of information. CRUD application created with help of sqlalchemy!!! – user1403505 Feb 12 '21 at 17:31

2 Answers2

1

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

user1403505
  • 895
  • 2
  • 19
  • 42
0

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.

Brian Burton
  • 3,648
  • 16
  • 29