Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Unable to read MySQL table without Primary Key #26

Open
szxyks opened this issue Jan 16, 2020 · 4 comments
Open

Unable to read MySQL table without Primary Key #26

szxyks opened this issue Jan 16, 2020 · 4 comments

Comments

@szxyks
Copy link

szxyks commented Jan 16, 2020

I created a simple table and ran a Dataflow job. But I got hit by this error:

sqlalchemy.exc.ArgumentError: Mapper mapped class TableClass->data could not assemble any primary key columns for mapped table 'data' [while running 'Reading from CloudSQLMySQL/ParDo(_ReadFromRelationalDBFn)']

Then I alter my table, I added a primary key then ran the job again and got succeeded.

It appears that beam_nuggets cannot read a table without primary key. If so, why is that? Is there a way to bypass this limitation since I'm trying to dump some tcp data into a table and there's no way to generate a primary key for that data.

Here is the full stacktrace of the error:

Traceback (most recent call last):
  File "apache_beam/runners/common.py", line 780, in apache_beam.runners.common.DoFnRunner.process
  File "apache_beam/runners/common.py", line 440, in apache_beam.runners.common.SimpleInvoker.invoke_process
  File "apache_beam/runners/common.py", line 895, in apache_beam.runners.common._OutputProcessor.process_outputs
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db.py", line 96, in process
    for record in db.query(table_name, query):
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 269, in query
    table = self._open_table_for_read(table_name)
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 304, in _open_table_for_read
    get_table_f=load_table
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 319, in _open_table
    self._get_table(name, get_table_f, **get_table_f_params)
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 325, in _get_table
    table_class = get_table_f(self._session, name, **get_table_f_params)
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 382, in load_table
    table_class = create_table_class(Table(name, metadata, autoload=True))
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 420, in create_table_class
    class TableClass(declarative_base()):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/api.py", line 75, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 131, in _as_declarative
    _MapperConfig.setup_mapping(cls, classname, dict_)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 160, in setup_mapping
    cfg_cls(cls_, classname, dict_)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 194, in __init__
    self._early_mapping()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 199, in _early_mapping
    self.map()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 696, in map
    self.cls, self.local_table, **self.mapper_args
  File "<string>", line 2, in mapper
  File "<string>", line 2, in __init__
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py", line 128, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 716, in __init__
    self._configure_pks()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 1397, in _configure_pks
    % (self, self.persist_selectable.description)
sqlalchemy.exc.ArgumentError: Mapper mapped class TableClass->data could not assemble any primary key columns for mapped table 'data'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/dataflow_worker/batchworker.py", line 650, in do_work
    work_executor.execute()
  File "/usr/local/lib/python3.6/site-packages/dataflow_worker/executor.py", line 176, in execute
    op.start()
  File "dataflow_worker/native_operations.py", line 38, in dataflow_worker.native_operations.NativeReadOperation.start
  File "dataflow_worker/native_operations.py", line 39, in dataflow_worker.native_operations.NativeReadOperation.start
  File "dataflow_worker/native_operations.py", line 44, in dataflow_worker.native_operations.NativeReadOperation.start
  File "dataflow_worker/native_operations.py", line 54, in dataflow_worker.native_operations.NativeReadOperation.start
  File "apache_beam/runners/worker/operations.py", line 256, in apache_beam.runners.worker.operations.Operation.output
  File "apache_beam/runners/worker/operations.py", line 143, in apache_beam.runners.worker.operations.SingletonConsumerSet.receive
  File "apache_beam/runners/worker/operations.py", line 593, in apache_beam.runners.worker.operations.DoOperation.process
  File "apache_beam/runners/worker/operations.py", line 594, in apache_beam.runners.worker.operations.DoOperation.process
  File "apache_beam/runners/common.py", line 776, in apache_beam.runners.common.DoFnRunner.receive
  File "apache_beam/runners/common.py", line 782, in apache_beam.runners.common.DoFnRunner.process
  File "apache_beam/runners/common.py", line 849, in apache_beam.runners.common.DoFnRunner._reraise_augmented
  File "/usr/local/lib/python3.6/site-packages/future/utils/__init__.py", line 421, in raise_with_traceback
    raise exc.with_traceback(traceback)
  File "apache_beam/runners/common.py", line 780, in apache_beam.runners.common.DoFnRunner.process
  File "apache_beam/runners/common.py", line 440, in apache_beam.runners.common.SimpleInvoker.invoke_process
  File "apache_beam/runners/common.py", line 895, in apache_beam.runners.common._OutputProcessor.process_outputs
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db.py", line 96, in process
    for record in db.query(table_name, query):
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 269, in query
    table = self._open_table_for_read(table_name)
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 304, in _open_table_for_read
    get_table_f=load_table
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 319, in _open_table
    self._get_table(name, get_table_f, **get_table_f_params)
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 325, in _get_table
    table_class = get_table_f(self._session, name, **get_table_f_params)
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 382, in load_table
    table_class = create_table_class(Table(name, metadata, autoload=True))
  File "/usr/local/lib/python3.6/site-packages/beam_nuggets/io/relational_db_api.py", line 420, in create_table_class
    class TableClass(declarative_base()):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/api.py", line 75, in __init__
    _as_declarative(cls, classname, cls.__dict__)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 131, in _as_declarative
    _MapperConfig.setup_mapping(cls, classname, dict_)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 160, in setup_mapping
    cfg_cls(cls_, classname, dict_)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 194, in __init__
    self._early_mapping()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 199, in _early_mapping
    self.map()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/ext/declarative/base.py", line 696, in map
    self.cls, self.local_table, **self.mapper_args
  File "<string>", line 2, in mapper
  File "<string>", line 2, in __init__
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/deprecations.py", line 128, in warned
    return fn(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 716, in __init__
    self._configure_pks()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/mapper.py", line 1397, in _configure_pks
    % (self, self.persist_selectable.description)
sqlalchemy.exc.ArgumentError: Mapper mapped class TableClass->data could not assemble any primary key columns for mapped table 'data' [while running 'Reading from CloudSQLMySQL/ParDo(_ReadFromRelationalDBFn)']
@szxyks
Copy link
Author

szxyks commented Feb 5, 2020

Any updates on this?

@2514millerj
Copy link

I believe this error is due to sqlalchemy, not beam-nuggets: https://docs.sqlalchemy.org/en/13/faq/ormconfiguration.html#how-do-i-map-a-table-that-has-no-primary-key. SQLAlchemy forces a primary key to be defined by the table, and there are a few tricks to work around it.

It is best practice when working with relational databases to have a primary key in every table.

@sann05
Copy link

sann05 commented May 4, 2020

I dealt with that with a wild workaround by creating a mock table with a primary key and put it into

relational_db.ReadFromDB(
            source_config=data_base.get_source_config(),
            table_name='mock_table_with_primary',
            query=data_base.needed_query())

It worked despite the fact I query 4 tables excluding the one I put.

Why can't I just query whatever I want (including cross-table joins etc...) from DB without putting table_name param?

@szxyks
Copy link
Author

szxyks commented May 4, 2020

Thanks for the workaround @sann05 I'll try it out.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@2514millerj @sann05 @szxyks