[BioMart Users] Adding a new attribute set to an existing mart

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[BioMart Users] Adding a new attribute set to an existing mart

Greenman, Ronald (NIH/CIT) [C]
I'm running a mart (based on the one used by PRIDE), but I need to add a new table to it. I'm currently calling it pride__spectrum__main, and I don't think I need to add any dimension tables for now. I'm having some trouble getting the API to recognize it, though. Although the filters and attributes I created show up in martview, attempting to use them results in a critical error: it says that it can't find the table 'main', which doesn't sound like it falls into the BioMart naming scheme.

I made a pride__spectrum__main table, with a primary key as the column 'spectrum__id__key', and added spectrum_id_ref columns in other tables as needed. I used marteditor to create an attribute set keyed to that table as well as a set of filters for it. Is there something else I need to do to make this work?
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] Adding a new attribute set to an existing mart

Arek Kasprzyk
Hi Ronald,
A few questions to help me with a 'diagnosis' of the problem:
Are you creating your mart from scratch or are you extending the existing pride mart?
Do you use double underscores for table names and single underscores for 'key' names?
Did your attributes show up in the naive configuration or did you add them manually later?

a

On Tue, Dec 20, 2011 at 9:24 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:
I'm running a mart (based on the one used by PRIDE), but I need to add a new table to it. I'm currently calling it pride__spectrum__main, and I don't think I need to add any dimension tables for now. I'm having some trouble getting the API to recognize it, though. Although the filters and attributes I created show up in martview, attempting to use them results in a critical error: it says that it can't find the table 'main', which doesn't sound like it falls into the BioMart naming scheme.

I made a pride__spectrum__main table, with a primary key as the column 'spectrum__id__key', and added spectrum_id_ref columns in other tables as needed. I used marteditor to create an attribute set keyed to that table as well as a set of filters for it. Is there something else I need to do to make this work?
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users


_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] Adding a new attribute set to an existing mart

Arek Kasprzyk
Hi Ronald,
there are certain rules when creating marts with multiple mains. You are probably aware of this, I just want to make sure that we exclude any potential problems with those:

Each 'submain' must 'inherit' all the columns from the the previous main up including the keys eg.

if the first main is 'gene__main' that has three columns including 'gene_key', then the next submain e.g transcript__main needs to have all the three columns from 'gene__main' plus any additional columns let's say 5  from transcript and its own key let's say 'transcript_key'. The next submain let's say 'protein__main would than have to have 8 columns including two keys 'gene_key' and 'transcript_key' plus its own key let's say 'protein_key' and any addtitional columns for protein__main.

hope this helps,
a


On Tue, Dec 20, 2011 at 4:57 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I’m extending the existing pride mart. Double underscores are used for table names, single underscores for keys. My attributes were added later: the ones on existing main tables worked fine, but this new main table didn’t.

 

I’ve actually made some progress since my last message: I needed to explicitly add pride__spectrum__main to the list of main tables and spectrum_id_key to the list of primary keys. This causes queries on that table to show up normally, as long as I only use only attributes and filters involving that table. However, if I use attributes or filters involving BOTH that table AND another table (say, pride__experiment__main), I get similar errors, only this time it says it can’t find “main” when referring to fields on the tables that worked before. Queries involving those old tables still work, as long as I don’t try to mix them with the old tables.

 

Single table, old: OK                                                                                       

Multiple tables, old only: OK

Single table, new: OK

Multiple tables, old and new: FAILED

(I only have one new table, do I can’t test what happens with multiple new tables)

 

For example, let’s say I have a query that filters on pride__sequence__main.ms_level and returns pride__sequence__main.ms_level, pride__sequence__main.range_start, and pride__sequence__main.range_stop. This works fine. If I add pride__experiment__main.experiment_ac, however, then martview says that it can’t find the column ‘main.experiment_ac’ in ‘field list’. This also happens in martexplorer.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 11:50 AM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
A few questions to help me with a 'diagnosis' of the problem:
Are you creating your mart from scratch or are you extending the existing pride mart?
Do you use double underscores for table names and single underscores for 'key' names?
Did your attributes show up in the naive configuration or did you add them manually later?

a

On Tue, Dec 20, 2011 at 9:24 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I'm running a mart (based on the one used by PRIDE), but I need to add a new table to it. I'm currently calling it pride__spectrum__main, and I don't think I need to add any dimension tables for now. I'm having some trouble getting the API to recognize it, though. Although the filters and attributes I created show up in martview, attempting to use them results in a critical error: it says that it can't find the table 'main', which doesn't sound like it falls into the BioMart naming scheme.

I made a pride__spectrum__main table, with a primary key as the column 'spectrum__id__key', and added spectrum_id_ref columns in other tables as needed. I used marteditor to create an attribute set keyed to that table as well as a set of filters for it. Is there something else I need to do to make this work?
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users

 



_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] Adding a new attribute set to an existing mart

Greenman, Ronald (NIH/CIT) [C]

Thanks. It looks like I actually do need to rework the hierarchy a little.

 

However, the spectra already do inherit one of the keys from higher in the hierarchy: the one from experiment__main. I mention this because after I sent yesterday’s e-mail, I found that the bug wasn’t as simple as I’d first thought: I can use queries that cross two tables for some attributes, but not others. Extending the example from the previous message, a query that involves some attributes from spectrum__main and also experiment__main.institution works, but a query that involves experiment__main.experiment_ac does not. What I find strange about this is that experiment__main.institution isn’t actually part of experiment__main; it’s in a dimension table (contact__dm, specifically). But that one works, while experiment_ac, which is actually in experiment__main, doesn’t.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 7:04 PM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
there are certain rules when creating marts with multiple mains. You are probably aware of this, I just want to make sure that we exclude any potential problems with those:

Each 'submain' must 'inherit' all the columns from the the previous main up including the keys eg.

if the first main is 'gene__main' that has three columns including 'gene_key', then the next submain e.g transcript__main needs to have all the three columns from 'gene__main' plus any additional columns let's say 5  from transcript and its own key let's say 'transcript_key'. The next submain let's say 'protein__main would than have to have 8 columns including two keys 'gene_key' and 'transcript_key' plus its own key let's say 'protein_key' and any addtitional columns for protein__main.

hope this helps,
a

On Tue, Dec 20, 2011 at 4:57 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I’m extending the existing pride mart. Double underscores are used for table names, single underscores for keys. My attributes were added later: the ones on existing main tables worked fine, but this new main table didn’t.

 

I’ve actually made some progress since my last message: I needed to explicitly add pride__spectrum__main to the list of main tables and spectrum_id_key to the list of primary keys. This causes queries on that table to show up normally, as long as I only use only attributes and filters involving that table. However, if I use attributes or filters involving BOTH that table AND another table (say, pride__experiment__main), I get similar errors, only this time it says it can’t find “main” when referring to fields on the tables that worked before. Queries involving those old tables still work, as long as I don’t try to mix them with the old tables.

 

Single table, old: OK                                                                                       

Multiple tables, old only: OK

Single table, new: OK

Multiple tables, old and new: FAILED

(I only have one new table, do I can’t test what happens with multiple new tables)

 

For example, let’s say I have a query that filters on pride__sequence__main.ms_level and returns pride__sequence__main.ms_level, pride__sequence__main.range_start, and pride__sequence__main.range_stop. This works fine. If I add pride__experiment__main.experiment_ac, however, then martview says that it can’t find the column ‘main.experiment_ac’ in ‘field list’. This also happens in martexplorer.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 11:50 AM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
A few questions to help me with a 'diagnosis' of the problem:
Are you creating your mart from scratch or are you extending the existing pride mart?
Do you use double underscores for table names and single underscores for 'key' names?
Did your attributes show up in the naive configuration or did you add them manually later?

a

On Tue, Dec 20, 2011 at 9:24 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I'm running a mart (based on the one used by PRIDE), but I need to add a new table to it. I'm currently calling it pride__spectrum__main, and I don't think I need to add any dimension tables for now. I'm having some trouble getting the API to recognize it, though. Although the filters and attributes I created show up in martview, attempting to use them results in a critical error: it says that it can't find the table 'main', which doesn't sound like it falls into the BioMart naming scheme.

I made a pride__spectrum__main table, with a primary key as the column 'spectrum__id__key', and added spectrum_id_ref columns in other tables as needed. I used marteditor to create an attribute set keyed to that table as well as a set of filters for it. Is there something else I need to do to make this work?
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users

 

 


_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] Adding a new attribute set to an existing mart

Arek Kasprzyk
Hi Ronald,
the SQL path through mart tables is resolved during run time based on the main table hierachy and the keys present in the dm tables. Dependent on the particular combination of dimensions different mains maybe be used. If you have your schema handy and could send me a few examples of queries that work and some of those that don't I'll try to look into it in more detail.  Perhaps I will be able to come up with a better advice

a

On Wed, Dec 21, 2011 at 9:08 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

Thanks. It looks like I actually do need to rework the hierarchy a little.

 

However, the spectra already do inherit one of the keys from higher in the hierarchy: the one from experiment__main. I mention this because after I sent yesterday’s e-mail, I found that the bug wasn’t as simple as I’d first thought: I can use queries that cross two tables for some attributes, but not others. Extending the example from the previous message, a query that involves some attributes from spectrum__main and also experiment__main.institution works, but a query that involves experiment__main.experiment_ac does not. What I find strange about this is that experiment__main.institution isn’t actually part of experiment__main; it’s in a dimension table (contact__dm, specifically). But that one works, while experiment_ac, which is actually in experiment__main, doesn’t.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 7:04 PM


To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
there are certain rules when creating marts with multiple mains. You are probably aware of this, I just want to make sure that we exclude any potential problems with those:

Each 'submain' must 'inherit' all the columns from the the previous main up including the keys eg.

if the first main is 'gene__main' that has three columns including 'gene_key', then the next submain e.g transcript__main needs to have all the three columns from 'gene__main' plus any additional columns let's say 5  from transcript and its own key let's say 'transcript_key'. The next submain let's say 'protein__main would than have to have 8 columns including two keys 'gene_key' and 'transcript_key' plus its own key let's say 'protein_key' and any addtitional columns for protein__main.

hope this helps,
a

On Tue, Dec 20, 2011 at 4:57 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I’m extending the existing pride mart. Double underscores are used for table names, single underscores for keys. My attributes were added later: the ones on existing main tables worked fine, but this new main table didn’t.

 

I’ve actually made some progress since my last message: I needed to explicitly add pride__spectrum__main to the list of main tables and spectrum_id_key to the list of primary keys. This causes queries on that table to show up normally, as long as I only use only attributes and filters involving that table. However, if I use attributes or filters involving BOTH that table AND another table (say, pride__experiment__main), I get similar errors, only this time it says it can’t find “main” when referring to fields on the tables that worked before. Queries involving those old tables still work, as long as I don’t try to mix them with the old tables.

 

Single table, old: OK                                                                                       

Multiple tables, old only: OK

Single table, new: OK

Multiple tables, old and new: FAILED

(I only have one new table, do I can’t test what happens with multiple new tables)

 

For example, let’s say I have a query that filters on pride__sequence__main.ms_level and returns pride__sequence__main.ms_level, pride__sequence__main.range_start, and pride__sequence__main.range_stop. This works fine. If I add pride__experiment__main.experiment_ac, however, then martview says that it can’t find the column ‘main.experiment_ac’ in ‘field list’. This also happens in martexplorer.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 11:50 AM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
A few questions to help me with a 'diagnosis' of the problem:
Are you creating your mart from scratch or are you extending the existing pride mart?
Do you use double underscores for table names and single underscores for 'key' names?
Did your attributes show up in the naive configuration or did you add them manually later?

a

On Tue, Dec 20, 2011 at 9:24 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I'm running a mart (based on the one used by PRIDE), but I need to add a new table to it. I'm currently calling it pride__spectrum__main, and I don't think I need to add any dimension tables for now. I'm having some trouble getting the API to recognize it, though. Although the filters and attributes I created show up in martview, attempting to use them results in a critical error: it says that it can't find the table 'main', which doesn't sound like it falls into the BioMart naming scheme.

I made a pride__spectrum__main table, with a primary key as the column 'spectrum__id__key', and added spectrum_id_ref columns in other tables as needed. I used marteditor to create an attribute set keyed to that table as well as a set of filters for it. Is there something else I need to do to make this work?
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users

 

 



_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] Adding a new attribute set to an existing mart

Greenman, Ronald (NIH/CIT) [C]

I worked out the inheritance hierarchy I wanted to use, and that seems to have fixed tables that cross the spectrum and experiment tables, but there are two other tables (proteinident and peptideident) that still show the error. I’ll include a simplified version of the schema (main tables only) at the end of this message.

 

Queries that require only the experiment table work fine, as do queries that require only the spectrum table. Queries that require the proteinident and peptideident tables work, but only if they don’t use the columns they inherited from the spectrum table.

 

CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__experiment__main (

    experiment_id_key INTEGER UNSIGNED NOT NULL,

    experiment_ac VARCHAR(8) NOT NULL,

    experiment_title TEXT NOT NULL,

    experiment_short_title VARCHAR(255) NULL,

    sample_name VARCHAR(255) NULL,

    sample_description_comment TEXT NULL,

    PRIMARY KEY(experiment_id_key)

);

 

CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__spectrum__main (

    spectrum_id_key INTEGER UNSIGNED NOT NULL,

    ms_level        INTEGER NOT NULL,

    range_start     DOUBLE PRECISION NULL,

    range_stop      DOUBLE PRECISION NULL,

    mz_data         TEXT NULL,

    intensity       TEXT NULL,

    -- inherited from experiment

    experiment_id_key INTEGER UNSIGNED NOT NULL,

    experiment_ac VARCHAR(8) NOT NULL,

    experiment_title TEXT NOT NULL,

    experiment_short_title VARCHAR(255) NULL,

    sample_name VARCHAR(255) NULL,

    sample_description_comment TEXT NULL,

    PRIMARY KEY(experiment_id_key, spectrum_id_key)

);

 

CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__proteinident__main (

    ident_id_key INTEGER NOT NULL,

    submitted_accession VARCHAR(50) NOT NULL,

    submitted_ac_protein_database VARCHAR(150) NULL,

    submitted_ac_protein_db_version VARCHAR(30) NULL,

    protein_sequence TEXT NULL,

    protein_spectrum_ref VARCHAR(100) NULL,

    protein_score DECIMAL(20,10) NULL,

    protein_threshold VARCHAR(255) NULL,

    search_engine VARCHAR(255) NULL,

    -- inherited from spectrum

    spectrum_id_key INTEGER UNSIGNED NOT NULL,

    ms_level        INTEGER NOT NULL,

    range_start     DOUBLE PRECISION NULL,

    range_stop      DOUBLE PRECISION NULL,

    mz_data         TEXT NULL,

    intensity       TEXT NULL,

    -- inherited from experiment

    experiment_id_key INTEGER UNSIGNED NOT NULL,

    experiment_ac VARCHAR(8) NOT NULL,

    experiment_title TEXT NOT NULL,

   experiment_short_title VARCHAR(255) NULL,

    sample_name VARCHAR(255) NULL,

    sample_description_comment TEXT NULL,

    PRIMARY KEY(experiment_id_key, spectrum_id_key, ident_id_key)

);

 

CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__peptideident__main (

    peptide_id_key INTEGER NOT NULL,

    prob    DOUBLE PRECISION    NULL,

    mascot  DOUBLE PRECISION    NULL,

    sequest DOUBLE PRECISION    NULL,

    xc      DOUBLE PRECISION    NULL,

    dcn     DOUBLE PRECISION    NULL,

    sp      DOUBLE PRECISION    NULL,

    rsp     DOUBLE PRECISION    NULL,

    ions    DOUBLE PRECISION    NULL,

    mass    DOUBLE PRECISION    NULL,

    charge  DOUBLE PRECISION    NULL,

    peptide_sequence VARCHAR(255) NULL,

    start_coord INTEGER NULL,

    end_coord INTEGER NULL,

    peptide_spectrum_ref VARCHAR(255) NULL,

    -- inherited from proteinident

    ident_id_key INTEGER NOT NULL,

    submitted_accession VARCHAR(50) NOT NULL,

    submitted_ac_protein_database VARCHAR(150) NULL,

    submitted_ac_protein_db_version VARCHAR(30) NULL,

    protein_sequence TEXT NULL,

    protein_spectrum_ref VARCHAR(100) NULL,

    protein_score DECIMAL(20,10) NULL,

    protein_threshold VARCHAR(255) NULL,

    search_engine VARCHAR(255) NULL,

    -- inherited from spectrum

    spectrum_id_key INTEGER UNSIGNED NOT NULL,

    ms_level        INTEGER NOT NULL,

    range_start     DOUBLE PRECISION NULL,

    range_stop      DOUBLE PRECISION NULL,

    mz_data         TEXT NULL,

    intensity       TEXT NULL,

    -- inherited from experiment

    experiment_id_key INTEGER UNSIGNED NOT NULL,

    experiment_ac VARCHAR(8) NOT NULL,

    experiment_title TEXT NOT NULL,

    experiment_short_title VARCHAR(255) NULL,

    sample_name VARCHAR(255) NULL,

    sample_description_comment TEXT NULL,

    PRIMARY KEY(experiment_id_key, spectrum_id_key, ident_id_key, peptide_id_key)

);

 

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Wednesday, December 21, 2011 4:03 PM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
the SQL path through mart tables is resolved during run time based on the main table hierachy and the keys present in the dm tables. Dependent on the particular combination of dimensions different mains maybe be used. If you have your schema handy and could send me a few examples of queries that work and some of those that don't I'll try to look into it in more detail.  Perhaps I will be able to come up with a better advice

a

On Wed, Dec 21, 2011 at 9:08 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

Thanks. It looks like I actually do need to rework the hierarchy a little.

 

However, the spectra already do inherit one of the keys from higher in the hierarchy: the one from experiment__main. I mention this because after I sent yesterday’s e-mail, I found that the bug wasn’t as simple as I’d first thought: I can use queries that cross two tables for some attributes, but not others. Extending the example from the previous message, a query that involves some attributes from spectrum__main and also experiment__main.institution works, but a query that involves experiment__main.experiment_ac does not. What I find strange about this is that experiment__main.institution isn’t actually part of experiment__main; it’s in a dimension table (contact__dm, specifically). But that one works, while experiment_ac, which is actually in experiment__main, doesn’t.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 7:04 PM


To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
there are certain rules when creating marts with multiple mains. You are probably aware of this, I just want to make sure that we exclude any potential problems with those:

Each 'submain' must 'inherit' all the columns from the the previous main up including the keys eg.

if the first main is 'gene__main' that has three columns including 'gene_key', then the next submain e.g transcript__main needs to have all the three columns from 'gene__main' plus any additional columns let's say 5  from transcript and its own key let's say 'transcript_key'. The next submain let's say 'protein__main would than have to have 8 columns including two keys 'gene_key' and 'transcript_key' plus its own key let's say 'protein_key' and any addtitional columns for protein__main.

hope this helps,
a

On Tue, Dec 20, 2011 at 4:57 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I’m extending the existing pride mart. Double underscores are used for table names, single underscores for keys. My attributes were added later: the ones on existing main tables worked fine, but this new main table didn’t.

 

I’ve actually made some progress since my last message: I needed to explicitly add pride__spectrum__main to the list of main tables and spectrum_id_key to the list of primary keys. This causes queries on that table to show up normally, as long as I only use only attributes and filters involving that table. However, if I use attributes or filters involving BOTH that table AND another table (say, pride__experiment__main), I get similar errors, only this time it says it can’t find “main” when referring to fields on the tables that worked before. Queries involving those old tables still work, as long as I don’t try to mix them with the old tables.

 

Single table, old: OK                                                                                       

Multiple tables, old only: OK

Single table, new: OK

Multiple tables, old and new: FAILED

(I only have one new table, do I can’t test what happens with multiple new tables)

 

For example, let’s say I have a query that filters on pride__sequence__main.ms_level and returns pride__sequence__main.ms_level, pride__sequence__main.range_start, and pride__sequence__main.range_stop. This works fine. If I add pride__experiment__main.experiment_ac, however, then martview says that it can’t find the column ‘main.experiment_ac’ in ‘field list’. This also happens in martexplorer.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 11:50 AM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
A few questions to help me with a 'diagnosis' of the problem:
Are you creating your mart from scratch or are you extending the existing pride mart?
Do you use double underscores for table names and single underscores for 'key' names?
Did your attributes show up in the naive configuration or did you add them manually later?

a

On Tue, Dec 20, 2011 at 9:24 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I'm running a mart (based on the one used by PRIDE), but I need to add a new table to it. I'm currently calling it pride__spectrum__main, and I don't think I need to add any dimension tables for now. I'm having some trouble getting the API to recognize it, though. Although the filters and attributes I created show up in martview, attempting to use them results in a critical error: it says that it can't find the table 'main', which doesn't sound like it falls into the BioMart naming scheme.

I made a pride__spectrum__main table, with a primary key as the column 'spectrum__id__key', and added spectrum_id_ref columns in other tables as needed. I used marteditor to create an attribute set keyed to that table as well as a set of filters for it. Is there something else I need to do to make this work?
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users

 

 

 


_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users
Reply | Threaded
Open this post in threaded view
|

Re: [BioMart Users] Adding a new attribute set to an existing mart

Arek Kasprzyk
Hi Ronald,
It will be difficult for me to diagnose the problem if you cannot run a test 'naive' configuration on your mart.The 'naive' configuration has built in logic of how to interpret the 'main hierarchy'. Is there a chance that you could copy all your main tables to a new mart and run it there? (you don't need to copy meta tables, they will be created for you on the first run).

a

On Tue, Dec 27, 2011 at 2:58 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I’m having some trouble reconfiguring the mart from scratch. I did, however, get BioMart to output the query:

 

SELECT main.experiment_title, main.peptide_sequence, main.mz_data FROM develpride.pride__spectrum__main main LIMIT 200

 

That explains the basic error: it’s using the wrong main table. It should be using pride__peptideident__main, a subclass of pride__spectrum__main (indirectly, as pride__proteinident__main comes between them in the hierarchy) which has the required attribute. That leads to the next question: something’s clearly wrong in my setup, but what about it might cause BioMart to think that peptide_sequence is in pride__spectrum__main?

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Friday, December 23, 2011 7:04 PM


To: Greenman, Ronald (NIH/CIT) [C]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 


ok, please let me know how it goes

a

On Fri, Dec 23, 2011 at 4:27 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

It seems to only happen if I work with single attributes from main tables.

 

I’ve been extending the existing PRIDE configuration. I’ll look into reconfiguring the mart from scratch.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Friday, December 23, 2011 4:26 PM


To: Greenman, Ronald (NIH/CIT) [C]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

hmm ... that's odd. does the error only show when you combine queries with dimension (if so please tell me what the keys are on those dimensions) or if you just ask for single attributes from mains?

and one more question: do you work with 'naive' configuration or do you extend the existing pride configuration (if the latter is the case, please try to reconfigure the mart from scratch and see if the error persist)

a


On Fri, Dec 23, 2011 at 4:15 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I’m not finding any broken SQL in the error log. This is the only error that seems to come up:

 

DBD::mysql::st execute failed: Unknown column ‘main.peptide_sequence’ in ‘field list’ at /home/greenmanrb/oldpride/biomart-perl/lib/BioMart/Dataset/TableSet.pm line 236.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Friday, December 23, 2011 3:10 PM


To: Greenman, Ronald (NIH/CIT) [C]

Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Ronald,
sorry one more thing. would you be able to send me the actual broken SQL that is being generated? you can get it from the apache error log

cheers,
a

On Thu, Dec 22, 2011 at 2:47 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I worked out the inheritance hierarchy I wanted to use, and that seems to have fixed tables that cross the spectrum and experiment tables, but there are two other tables (proteinident and peptideident) that still show the error. I’ll include a simplified version of the schema (main tables only) at the end of this message.

 

Queries that require only the experiment table work fine, as do queries that require only the spectrum table. Queries that require the proteinident and peptideident tables work, but only if they don’t use the columns they inherited from the spectrum table.

 

CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__experiment__main (

    experiment_id_key INTEGER UNSIGNED NOT NULL,

    experiment_ac VARCHAR(8) NOT NULL,

    experiment_title TEXT NOT NULL,

    experiment_short_title VARCHAR(255) NULL,

    sample_name VARCHAR(255) NULL,

    sample_description_comment TEXT NULL,

    PRIMARY KEY(experiment_id_key)

);

 

CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__spectrum__main (

    spectrum_id_key INTEGER UNSIGNED NOT NULL,

    ms_level        INTEGER NOT NULL,

    range_start     DOUBLE PRECISION NULL,

    range_stop      DOUBLE PRECISION NULL,

    mz_data         TEXT NULL,

    intensity       TEXT NULL,

    -- inherited from experiment

    experiment_id_key INTEGER UNSIGNED NOT NULL,

    experiment_ac VARCHAR(8) NOT NULL,

    experiment_title TEXT NOT NULL,

    experiment_short_title VARCHAR(255) NULL,

    sample_name VARCHAR(255) NULL,

    sample_description_comment TEXT NULL,

    PRIMARY KEY(experiment_id_key, spectrum_id_key)

);

 

CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__proteinident__main (

    ident_id_key INTEGER NOT NULL,

    submitted_accession VARCHAR(50) NOT NULL,

    submitted_ac_protein_database VARCHAR(150) NULL,

    submitted_ac_protein_db_version VARCHAR(30) NULL,

    protein_sequence TEXT NULL,

    protein_spectrum_ref VARCHAR(100) NULL,

    protein_score DECIMAL(20,10) NULL,

    protein_threshold VARCHAR(255) NULL,

    search_engine VARCHAR(255) NULL,

    -- inherited from spectrum

    spectrum_id_key INTEGER UNSIGNED NOT NULL,

    ms_level        INTEGER NOT NULL,

    range_start     DOUBLE PRECISION NULL,

    range_stop      DOUBLE PRECISION NULL,

    mz_data         TEXT NULL,

    intensity       TEXT NULL,

    -- inherited from experiment

    experiment_id_key INTEGER UNSIGNED NOT NULL,

    experiment_ac VARCHAR(8) NOT NULL,

    experiment_title TEXT NOT NULL,

   experiment_short_title VARCHAR(255) NULL,

    sample_name VARCHAR(255) NULL,

    sample_description_comment TEXT NULL,

    PRIMARY KEY(experiment_id_key, spectrum_id_key, ident_id_key)

);

 

CREATE TABLE PRIDE_BIOMART_SCHEMA_NAME.pride__peptideident__main (

    peptide_id_key INTEGER NOT NULL,

    prob    DOUBLE PRECISION    NULL,

    mascot  DOUBLE PRECISION    NULL,

    sequest DOUBLE PRECISION    NULL,

    xc      DOUBLE PRECISION    NULL,

    dcn     DOUBLE PRECISION    NULL,

    sp      DOUBLE PRECISION    NULL,

    rsp     DOUBLE PRECISION    NULL,

    ions    DOUBLE PRECISION    NULL,

    mass    DOUBLE PRECISION    NULL,

    charge  DOUBLE PRECISION    NULL,

    peptide_sequence VARCHAR(255) NULL,

    start_coord INTEGER NULL,

    end_coord INTEGER NULL,

    peptide_spectrum_ref VARCHAR(255) NULL,

    -- inherited from proteinident

    ident_id_key INTEGER NOT NULL,

    submitted_accession VARCHAR(50) NOT NULL,

    submitted_ac_protein_database VARCHAR(150) NULL,

    submitted_ac_protein_db_version VARCHAR(30) NULL,

    protein_sequence TEXT NULL,

    protein_spectrum_ref VARCHAR(100) NULL,

    protein_score DECIMAL(20,10) NULL,

    protein_threshold VARCHAR(255) NULL,

    search_engine VARCHAR(255) NULL,

    -- inherited from spectrum

    spectrum_id_key INTEGER UNSIGNED NOT NULL,

    ms_level        INTEGER NOT NULL,

    range_start     DOUBLE PRECISION NULL,

    range_stop      DOUBLE PRECISION NULL,

    mz_data         TEXT NULL,

    intensity       TEXT NULL,

    -- inherited from experiment

    experiment_id_key INTEGER UNSIGNED NOT NULL,

    experiment_ac VARCHAR(8) NOT NULL,

    experiment_title TEXT NOT NULL,

    experiment_short_title VARCHAR(255) NULL,

    sample_name VARCHAR(255) NULL,

    sample_description_comment TEXT NULL,

    PRIMARY KEY(experiment_id_key, spectrum_id_key, ident_id_key, peptide_id_key)

);

 

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Wednesday, December 21, 2011 4:03 PM


To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
the SQL path through mart tables is resolved during run time based on the main table hierachy and the keys present in the dm tables. Dependent on the particular combination of dimensions different mains maybe be used. If you have your schema handy and could send me a few examples of queries that work and some of those that don't I'll try to look into it in more detail.  Perhaps I will be able to come up with a better advice

a

On Wed, Dec 21, 2011 at 9:08 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

Thanks. It looks like I actually do need to rework the hierarchy a little.

 

However, the spectra already do inherit one of the keys from higher in the hierarchy: the one from experiment__main. I mention this because after I sent yesterday’s e-mail, I found that the bug wasn’t as simple as I’d first thought: I can use queries that cross two tables for some attributes, but not others. Extending the example from the previous message, a query that involves some attributes from spectrum__main and also experiment__main.institution works, but a query that involves experiment__main.experiment_ac does not. What I find strange about this is that experiment__main.institution isn’t actually part of experiment__main; it’s in a dimension table (contact__dm, specifically). But that one works, while experiment_ac, which is actually in experiment__main, doesn’t.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 7:04 PM


To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
there are certain rules when creating marts with multiple mains. You are probably aware of this, I just want to make sure that we exclude any potential problems with those:

Each 'submain' must 'inherit' all the columns from the the previous main up including the keys eg.

if the first main is 'gene__main' that has three columns including 'gene_key', then the next submain e.g transcript__main needs to have all the three columns from 'gene__main' plus any additional columns let's say 5  from transcript and its own key let's say 'transcript_key'. The next submain let's say 'protein__main would than have to have 8 columns including two keys 'gene_key' and 'transcript_key' plus its own key let's say 'protein_key' and any addtitional columns for protein__main.

hope this helps,
a

On Tue, Dec 20, 2011 at 4:57 PM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I’m extending the existing pride mart. Double underscores are used for table names, single underscores for keys. My attributes were added later: the ones on existing main tables worked fine, but this new main table didn’t.

 

I’ve actually made some progress since my last message: I needed to explicitly add pride__spectrum__main to the list of main tables and spectrum_id_key to the list of primary keys. This causes queries on that table to show up normally, as long as I only use only attributes and filters involving that table. However, if I use attributes or filters involving BOTH that table AND another table (say, pride__experiment__main), I get similar errors, only this time it says it can’t find “main” when referring to fields on the tables that worked before. Queries involving those old tables still work, as long as I don’t try to mix them with the old tables.

 

Single table, old: OK                                                                                       

Multiple tables, old only: OK

Single table, new: OK

Multiple tables, old and new: FAILED

(I only have one new table, do I can’t test what happens with multiple new tables)

 

For example, let’s say I have a query that filters on pride__sequence__main.ms_level and returns pride__sequence__main.ms_level, pride__sequence__main.range_start, and pride__sequence__main.range_stop. This works fine. If I add pride__experiment__main.experiment_ac, however, then martview says that it can’t find the column ‘main.experiment_ac’ in ‘field list’. This also happens in martexplorer.

 

From: Arek Kasprzyk [mailto:[hidden email]]
Sent: Tuesday, December 20, 2011 11:50 AM
To: Greenman, Ronald (NIH/CIT) [C]
Cc: [hidden email]
Subject: Re: [BioMart Users] Adding a new attribute set to an existing mart

 

Hi Ronald,
A few questions to help me with a 'diagnosis' of the problem:
Are you creating your mart from scratch or are you extending the existing pride mart?
Do you use double underscores for table names and single underscores for 'key' names?
Did your attributes show up in the naive configuration or did you add them manually later?

a

On Tue, Dec 20, 2011 at 9:24 AM, Greenman, Ronald (NIH/CIT) [C] <[hidden email]> wrote:

I'm running a mart (based on the one used by PRIDE), but I need to add a new table to it. I'm currently calling it pride__spectrum__main, and I don't think I need to add any dimension tables for now. I'm having some trouble getting the API to recognize it, though. Although the filters and attributes I created show up in martview, attempting to use them results in a critical error: it says that it can't find the table 'main', which doesn't sound like it falls into the BioMart naming scheme.

I made a pride__spectrum__main table, with a primary key as the column 'spectrum__id__key', and added spectrum_id_ref columns in other tables as needed. I used marteditor to create an attribute set keyed to that table as well as a set of filters for it. Is there something else I need to do to make this work?
_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users

 

 

 

 

 

 



_______________________________________________
Users mailing list
[hidden email]
https://lists.biomart.org/mailman/listinfo/users