MySQL :: HeatWave User Guide :: 4.8.8 ML_EMBED

HeatWave User Guide / ... / ML_EMBED_TABLE

4.8.8 ML_EMBED_TABLE

The ML_EMBED_TABLE routine runs multiple embedding generations in a batch, in parallel.

Note

In versions older than MySQL 9.2.1, to alter an existing table or create a new table, MySQL requires you to set the sql-require-primary-key system variable to 0.

This routine is available in MySQL 9.0.1-u1 and later versions.

To learn about the privileges you need to run this routine, see Required Privileges.

ML_EMBED_TABLE Syntax

Press CTRL+C to copy
 
mysql> call sys.ML_EMBED_TABLE('InputTableColumn', 'OutputTableColumn', [options]);
    
options: {
  JSON_OBJECT('key','value'[,'key','value'] ...)
    'key','value': {
    ['model_id', {'all_minilm_l12_v2'|'minilm'|'multilingual-e5-small'|'cohere.embed-english-v3.0'|'cohere.embed-multilingual-v3.0'}]
    ['truncate', {true|false}]
    ['batch_size', BatchSize]
    }
}

Following are ML_EMBED_TABLE parameters:

InputTableColumn: specifies the names of the input database, table, and column that contains the text to encode. The InputTableColumn is specified in the following format: DBName.TableName.ColumnName.
- The specified input table can be an internal or external table.
- The specified input table must already exist, must not be empty, and must have a primary key.
- The input column must already exist and must contain text or varchar values.
- The input column must not be a part of the primary key and must not have NULL values or empty strings.
- There must be no backticks used in the DBName, TableName, or ColumnName and there must be no period used in the DBName or TableName.
OutputTableColumn: specifies the names of the database, table, and column where the generated embeddings are stored. The OutputTableColumn is specified in the following format: DBName.TableName.ColumnName.
- The specified output table must be an internal table.
- If the specified output table already exists, then it must be the same as the input table. And, the specified output column must not already exist in the input table. A new VECTOR column is added to the table. External tables are read only. So if input table is an external table, then it cannot be used to store the output.
- If the specified output table doesn't exist, then a new table is created. The new output table has key columns which contains the same primary key values as the input table and a VECTOR column that stores the generated embeddings.
- There must be no backticks used in the DBName, TableName, or ColumnName and there must be no period used in the DBName or TableName.
options: specifies optional parameters as key-value pairs in JSON format. It can include the following parameters:
- model_id: specifies the embedding model to use for encoding the text. Default value is all_minilm_l12_v2. Possible values are:
  - all_minilm_l12_v2 or minilm:
    
    As of MySQL 9.2.1, for encoding text or files in any supported language.
    
    In previous versions of MySQL, for encoding text or files in English only.
  - multilingual-e5-small:
    
    as of MySQL 9.2.1, for encoding text or files in any supported language.
    
    In previous versions of MySQL, for encoding text or files in supported languages other than English.
    
    This embedding model is available in MySQL 9.0.1-u1 and later versions.
  - cohere.embed-english-v3.0:
    
    As of MySQL 9.2.1, for encoding text or files in any supported language.
    
    In previous versions of MySQL, for encoding text or files in English only.
    
    This embedding model is available in MySQL 9.0.1-u1 and later versions.
  - cohere.embed-multilingual-v3.0:
    
    As of MySQL 9.2.1, for encoding text or files in any supported language.
    
    In previous versions of MySQL, for encoding text or files in supported languages other than English.
    
    This embedding model is available in MySQL 9.0.1-u1 and later versions.
  To view the lists of available embedding models, see HeatWave In-Database Embedding Models and OCI Generative AI Service Embedding Models. To view the list of supported languages, see Languages.
- truncate: specifies whether to truncate inputs longer than the maximum token size. Default value is true.
- batch_size: specifies the batch size for the routine. This parameter is supported for internal tables only. Default value is 1000. Possible values are integer values between 1 and 1000.

Syntax Examples

Consider the following input table demo_db.input_table:

Press CTRL+C to copy
 
mysql> select * from demo_db.input_table;
+----+----------------------------------+
| id | Input                            |
+----+----------------------------------+
|  1 | What is artificial intelligence? |
|  2 | What is MySQL?                   |
+----+----------------------------------+

Generate embeddings for text stored in demo_db.input_table.Input using the all_minilm_l12_v2 embedding model, and save the generated embeddings in the output table demo_db.output_table.Output:

Press CTRL+C to copy
 
mysql> call sys.ML_EMBED_TABLE("demo_db.input_table.Input", "demo_db.output_table.Output", JSON_OBJECT("model_id", "all_minilm_l12_v2"));

The output table contains the following fields:

Press CTRL+C to copy
 
mysql> describe demo_db.output_table;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id     | int          | NO   | PRI | NULL    |       |
| Output | vector(2048) | NO   |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

View the contents of the output table:

Press CTRL+C to copy
 
mysql> select * from demo_db.output_table;
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | Output
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 0x97325DBC090E45BDD46399BCC4147C3C73DADFBCE23F41BDB10E1B3D1F5F7D3DE0BEFD3C9A362E3D4258383D643E7ABC4E0730BCBEE9CE3CC8ED60BD92414DBB1EDDF0BB0B3508BD50C850BC018F06BE03AD20BBCA2C203DC264FC3ACB6C86BCFC6A66BC9B3D053DB42E143D21E7D73CD390F83CFF77D1BCE21CDA3A5DA9F6BC74461C3EA1D8963DF05214BDDA28DABB6ED1CD3BA77910BD158D643DDECD19BD642D2CBDEDBF1FBEFC0005BC6D4A163D5ED7123DD7D08D3DC08BABBD796E26BCF1A64BBDD3BE89BA968DB5BD530ECDBC302C5BBB00F3803D7CFD223D30AB96BCE3D802BCEC1198BCF300CE3CE922853B04C4613DB0C5093DAC1A6E3DA1436F3C61ABE73B1DFFE63CE45CD33C6591883B49C6953B97F630BDD6FD9E3D51509F3B156BC63C5CA7F13CD0F9423D77461E3DDF012F3DA7715ABDB0ECAE3D1D7F56BCA1B4B5BCF8C303BD0908CBBCB52E0B3E40FDF23A6C8023BDBF3A8DBD043CDA3CB017053EA82226BC6632973DFD1303BDC60DC63C7D13A63CA667D43D0CAABF3D310075BDD0A2AEBD9ED6D0BBF5349D3D50FB0FBDDC29903CD6B0F1BC6E4BA5BD3007BF3C6923B83C2F4D75BDD62B87BDBFF202BD198F86BCCB863BBDE6B189BC81AA2B3D14CF1CBC85B1763D9F8134BD8678C2BC2D5D2F3DA2B763BD43B515BD2AAA25BD3A05193DD68EBDBB9DACF83D1B73583D49D3FBBCB9F915BDD69A043D1F961EBD2E2E81BDAACD46BDF8EF823B804339BD199BB1BD29C2393D1803183C42A9DABDB1DE13BD74CFE63C93EB103C09F8403C34A9A93D4C4F8F3DC9AB4CBD433ADBBCDC9FCDBC59DBDD3C2AC8F8BC710F983DC42B1BBCC450053C83CC8BBD0F5DAB3C59A54BB9F4AB013D67C562BCDB3F8D3D086B153AE6FBF03B3C5D0D3D262D5E3B4F92E43C96DF953D5144B6399194933DDAA29ABCC2F52F3D2E44BFBBE807D43CA4684A3D33D9523D77D78E3DE226C13C016DA63CA0750B3DE277D0BCFD0F81BC9E9F3F3DB9D74FBC48748C3BA65A13BDA5AE32BCDBB921BC323A203DD27CB43C48B35B3C545DDE3CDAF611BC503AF73B02D9443D1068963C257E0F3E13C0213DA1BF55BC2AB484BC1D57F4BD8F40B33DB1FEA93DACDC843D1A9B08BC665BB7BDCA3B64BCF5C407BEFB884F3AF002F7BC7F47A5BDA17BFF3B1F373EBD7D3D1DBD2F6E953D3F0595BC9EBA2CBDF2E4053D683E80BDCE0C583B3CDE6ABB22035FBB64A2033CC3465BBDC08A2BBD30596B3CBEAA253D4614EDBD4E0B6A896534DCBDC66325BD8DDC97BDABB4D53D165F233D5591D23C2B3827BD6BBEDC3C0878A13D69FB8ABC2ACE62BDFB86093DA43C0CBC8531E73C1EEE74BD6D8B563C0A2571BD3E9917BDCA6BEC3C6716453D16C1DA3DE211AE3D7E3B50BCBACA6D3B0BBCA5BDE191203DD04DEF3B8B32D53C910ACF3CAF9C5B3CCB2E8C3C17AE39BDC6361ABDB67CBB3DFA0CCCBC26B0C23D5D88903C0B58B7BD54A3573C595D83BDDB18E33CD490C8BC1F9CB1BD8DE4CE3BC8736B3DC83716BC97856BBD1FBF1B3DD7AC783C1D45EDBC226D06BD870E66BCAFB1013D46C552BD65CD073D192EF5BC963557BDD626263CF2E6BABCB011E83D714E193D4464763C7BC5E4BC888B353DBC0FC4BD39BDEF3D1882DABBE61154BD9CCD1DBBC5036CBD7860E23DE962303D9BB6603DC4D80DBD8BCBCDBD80D0173D109B90BC361E773C0D34273DDD201A3C3CC76DBD65D7DFBDFAF480BCBF9627BD744488BCF4085D3C3CBAC1BDAD7FBFBBFB0B82BDAC69B13B38E772BC6DA512BD040F9DBD62CAED3C56A7B03BB28B4F08A87D393DC85BABBC264440BCF6385A3C5C831CBDAE9C12BC04485BBBB9535E3A129D24BD403AD0BDE828223D472457BD5957363D5B841E3EACC98C3C76561F3D218B25BCB9D51F3BBA870EBC62F235BD66478C3D9E73B6BC17AAEFBD306DC2BD5574A13CD1A962BDECB9FA3B200A2B3D0EA87DBB8FFC30BCECBFF4BCE42E84BB2BF820BD5FD7653D20C837BD02832D3BCF322F3C4C8B88BB91227DBCF95EB7BDC260D6BCE35B033E68C0F33B03E857BDA601A33D8BCE013D01910A3CC26ECDBD45839FBB2A1067BC1D12153C7607E6BC178F57BCE6ED563D5A21413D38E08A3DF1A74F3D6FDBBBBDEDC878BDDCD1A43D050681BCFE58383D2C8F913D3C458ABD |
|  2 | 0x1AA84F3C9125FEBC54DEA1BDC68C0ABCB624A9BD7FDF54BD4392513DA7F81A3D855FC33CE376F43CD73D67BDA65F1A3D4829C93DA8FA86BD08016FBC5D6676BCF02893BB9967C2BD302C8DBAF413A1BDF9A9E6BD68070DBD643E49BDF1ABB23C60E09EBCD8372EBD4F1AFDBC54CFD7BC2DC583BDD584CCBC2F105B3C211BE33DCD52D63DAD82813D42707D3C5A75D2BCD44C903CD1D1353D995D59BCC257FABC16E538BDB9BD7E3935653FBD9FDED53CA92C603DA2B726BCF9862C3DCE5B1CBDA5E926BCBB08393DE1AD36BCB6F277BC18A79FBDBBDEA53DF9BB6E3DAD84753DC04D2D3C26DD28BDA41FECBD771380BD09D5663D4A2D623D7D080E3CDD107A3DBA28CDBBC692F23C061E673DE4D62D3DC207E33B971566BDEEA7B4BD48092DBDBA01743C1B04BF3D7478DEBC0E5E9DBDBFBA7B3C51F2A3BC3A20043B52789E3B005B5D3CD5FBBA3C3DA4553C02D1AD3C8AD912BD51BDB2BDE51E7F3C588D56BB62EFA1BA8894023D1DF3203C781265BC986CD5BC2FC783BD25AE0ABC51B3A83CAD0B483A5E99A4BDCB26883D61BCAD3DF1FD3A3D354BB4BCE8A7883C3B6FF8BC460323BD9F92BB3DE240883D464B53BD70C7A5BCCB8F9BBD52831BBD06EACB3CB3043A3C203465BD52CF163E0CB903BDE6E614BDB6AC783D390181BD0534153DAB0001BDD4D8E73B2114943CF819D9BBBB6512BD54CCD5BB929342BD7C3FCA3D7CB7F6BCF4C7B7BAEA7E69BD9E2FFB3BC0A409B99FBB7E3D2A7435BC968896BC742290BDCF057E3CD31E263D658FD1BAFC1AFA3B888E303C3DB10C3D06822CBD034E82BDF16A963C73D81F3DDD5875BCDB1986BDC0B1DABA4E2B2A3B0C87233D88770A3E91BF343DA1B3C5BD2D337BBD9B6CF83C99AE773CEAF6D03C73512DBD9AE4E2BD4FAB5FBD7DA61EBDC4D0A83DE71198BD2E01C9BCCC19483C08B729BD97404B3DFFEE72BBD4E47B3DBF3AD0BB433498BD80EC353D7B0CBEBD8E3CF8BCF9218DBDB2C324BDDCF8C33B630394BD8D269F3D0BC6033DBA766F3DC9EA033DB55A0E3DD66505BD9C939BBDF06AAC3C1253CF3D45CC093D461751BC0C0CCD3D9015AA3DD2DF7EBC2B444A3BFF2BA5BDC6B3F03C66CC1F3D48876C3D703203BD2C0B253C1DDCDB3D0B44EFBD88F0D83D6B30D0BCC5FC863C7F4595BC51ABE5BCFFEC79BDDAB01ABDF93587BDB40EFC3D1E2F16BD1AA2A5BBBA39BA3C3556D13CEA68383D2E49C83CCD1CA6BDF6A2383C18E15B3DBABB8C3CFFE942BD1729320AAB1E2CBD4FE003BCC67F9BBD9F43503D0F4681BB3313D73C72D8F43ACCF656BC4C81393D1F2C1D3D6F5A81BB5510E03DF5672F3D8E73CCBB79A2F7BC329F43BCE017B13C2328AFBD567DA83A4A7C113DA23829BC1FC09C3DB0E6E5BCA811DD3CC68EDF3CDB2B0D3D5A90263BE743ACBD59D3893C99C1153D02760EBD365659BD9BCFB63CF007BF3DC3D911BD74FD4CBD01757C3CF05949BD0F427C3D4BE18BBDD7420FBD0CAF24BC9D5DC2BA6DC4BF3C26DD893CD94DB83D47330CBD5ECA3A3D82EA8E3C434568BC0DC178BD2C14E63B7E4F9F3DD39383BD566A403C4799B9BC698BDABCC20DD6BBAE7499BDBD252ABDA6ED873DF6EAA3BD61FC7FBDC87F7D3D241EB5BC70BCB93D0842613CEAA82DBB55AB423CE9DE133D43E51E3C041C75BCEECA033D8EFD4F3919A45CBD1041F13D6C18963AC1FCD33D6C3E843D57E72D3C4137933ADB1DB2BBC07A8D3C5EA5233DAC3AAA3C2C03A0BD3DF1AE3B7233913D522B043C876C06BCE842713D97E41FBD972203BE1367043DF46D6BBDBD2E038AE450483D3B2DAABCCC3F203C9E2EF2BC4D687C3D85659CBD4028BF3C750AEA3D5C57413DDE1BC7BCC9E24C3DB664C7BDDBC2BF39AFC6293C847CB23C1D4A063D96DD38BC94CE263CF8757B3DD42907BDBF7BDB3DB575983C637418BCA3F180BD2EB1B83D038F953B331ECC3B68189A3D06A5813D5F0C55BD1DC69CBB21F8353C8CAA1EBDD78607BDEDA683BBE712A4BD1B17423DF3600FBD44DA09BD53A8993CE1D3E73D0E75F23DAC9B173D3F0FF93ADDF5CDBC91D4023D80C3943C7DC3AE3C332F533D521A3DBD364ECDBDC0E06ABC944C753D281A97BA41E78EBD1A373FBCCBD5123D996AE2BD4BDD413DD6A05CBD9DA293BCEA48903BE42F313D88F50ABC |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

As of MySQL 9.2.1, to specify the embedding model used to generate the vector embeddings, the routine adds the following comment for the VECTOR column in the output table:

Press CTRL+C to copy
 
'GENAI_OPTIONS=EMBED_MODEL_ID=EmbeddingModelID'

For example:

Press CTRL+C to copy
 
mysql>SHOW CREATE TABLE output_table;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                           |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| output_table | CREATE TABLE `output_table` (
  `id` int NOT NULL DEFAULT '0',
  `Output` vector(2048) NOT NULL COMMENT 'GENAI_OPTIONS=EMBED_MODEL_ID=minilm',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

PREV HOME UP NEXT