MySQL :: HeatWave User Guide :: 4.7.8 ML_EMBED

HeatWave User Guide / ... / ML_EMBED_TABLE

4.7.8 ML_EMBED_TABLE

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

Note

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

mysql> call sys.ML_EMBED_TABLE('InputTableColumn', 'OutputTableColumn', [options]);
    
options: {
  JSON_OBJECT('key','value'[,'key','value'] ...)
    'key','value': {
    ['model_id', {'all_minilm_l12_v2'|'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: for encoding English text.
  - multilingual-e5-small: for encoding text in supported languages other than English.
  - cohere.embed-english-v3.0: for encoding English text.
  - cohere.embed-multilingual-v3.0: for encoding text in supported languages other than English.
  To view the lists of supported 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:

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:

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:

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:

mysql> select * from demo_db.output_table;
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | Output
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | 0x97325DBC090E45BDD46399BCC4147C3C73DADFBCE23F41BDB10E1B3D1F5F7D3DE0BEFD3C9A362E3D4258383D643E7ABC4E0730BCBEE9CE3CC8ED60BD92414DBB1EDDF0BB0B3508BD50C850BC018F06BE03AD20BBCA2C203DC264FC3ACB6C86BCFC6A66BC9B3D053DB42E143D21E7D73CD390F83CFF77D1BCE21CDA3A5DA9F6BC74461C3EA1D8963DF05214BDDA28DABB6ED1CD3BA77910BD158D643DDECD19BD642D2CBDEDBF1FBEFC0005BC6D4A163D5ED7123DD7D08D3DC08BABBD796E26BCF1A64BBDD3BE89BA968DB5BD530ECDBC302C5BBB00F3803D7CFD223D30AB96BCE3D802BCEC1198BCF300CE3CE922853B04C4613DB0C5093DAC1A6E3DA1436F3C61ABE73B1DFFE63CE45CD33C6591883B49C6953B97F630BDD6FD9E3D51509F3B156BC63C5CA7F13CD0F9423D77461E3DDF012F3DA7715ABDB0ECAE3D1D7F56BCA1B4B5BCF8C303BD0908CBBCB52E0B3E40FDF23A6C8023BDBF3A8DBD043CDA3CB017053EA82226BC6632973DFD1303BDC60DC63C7D13A63CA667D43D0CAABF3D310075BDD0A2AEBD9ED6D0BBF5349D3D50FB0FBDDC29903CD6B0F1BC6E4BA5BD3007BF3C6923B83C2F4D75BDD62B87BDBFF202BD198F86BCCB863BBDE6B189BC81AA2B3D14CF1CBC85B1763D9F8134BD8678C2BC2D5D2F3DA2B763BD43B515BD2AAA25BD3A05193DD68EBDBB9DACF83D1B73583D49D3FBBCB9F915BDD69A043D1F961EBD2E2E81BDAACD46BDF8EF823B804339BD199BB1BD29C2393D1803183C42A9DABDB1DE13BD74CFE63C93EB103C09F8403C34A9A93D4C4F8F3DC9AB4CBD433ADBBCDC9FCDBC59DBDD3C2AC8F8BC710F983DC42B1BBCC450053C83CC8BBD0F5DAB3C59A54BB9F4AB013D67C562BCDB3F8D3D086B153AE6FBF03B3C5D0D3D262D5E3B4F92E43C96DF953D5144B6399194933DDAA29ABCC2F52F3D2E44BFBBE807D43CA4684A3D33D9523D77D78E3DE226C13C016DA63CA0750B3DE277D0BCFD0F81BC9E9F3F3DB9D74FBC48748C3BA65A13BDA5AE32BCDBB921BC323A203DD27CB43C48B35B3C545DDE3CDAF611BC503AF73B02D9443D1068963C257E0F3E13C0213DA1BF55BC2AB484BC1D57F4BD8F40B33DB1FEA93DACDC843D1A9B08BC665BB7BDCA3B64BCF5C407BEFB884F3AF002F7BC7F47A5BDA17BFF3B1F373EBD7D3D1DBD2F6E953D3F0595BC9EBA2CBDF2E4053D683E80BDCE0C583B3CDE6ABB22035FBB64A2033CC3465BBDC08A2BBD30596B3CBEAA253D4614EDBD4E0B6A896534DCBDC66325BD8DDC97BDABB4D53D165F233D5591D23C2B3827BD6BBEDC3C0878A13D69FB8ABC2ACE62BDFB86093DA43C0CBC8531E73C1EEE74BD6D8B563C0A2571BD3E9917BDCA6BEC3C6716453D16C1DA3DE211AE3D7E3B50BCBACA6D3B0BBCA5BDE191203DD04DEF3B8B32D53C910ACF3CAF9C5B3CCB2E8C3C17AE39BDC6361ABDB67CBB3DFA0CCCBC26B0C23D5D88903C0B58B7BD54A3573C595D83BDDB18E33CD490C8BC1F9CB1BD8DE4CE3BC8736B3DC83716BC97856BBD1FBF1B3DD7AC783C1D45EDBC226D06BD870E66BCAFB1013D46C552BD65CD073D192EF5BC963557BDD626263CF2E6BABCB011E83D714E193D4464763C7BC5E4BC888B353DBC0FC4BD39BDEF3D1882DABBE61154BD9CCD1DBBC5036CBD7860E23DE962303D9BB6603DC4D80DBD8BCBCDBD80D0173D109B90BC361E773C0D34273DDD201A3C3CC76DBD65D7DFBDFAF480BCBF9627BD744488BCF4085D3C3CBAC1BDAD7FBFBBFB0B82BDAC69B13B38E772BC6DA512BD040F9DBD62CAED3C56A7B03BB28B4F08A87D393DC85BABBC264440BCF6385A3C5C831CBDAE9C12BC04485BBBB9535E3A129D24BD403AD0BDE828223D472457BD5957363D5B841E3EACC98C3C76561F3D218B25BCB9D51F3BBA870EBC62F235BD66478C3D9E73B6BC17AAEFBD306DC2BD5574A13CD1A962BDECB9FA3B200A2B3D0EA87DBB8FFC30BCECBFF4BCE42E84BB2BF820BD5FD7653D20C837BD02832D3BCF322F3C4C8B88BB91227DBCF95EB7BDC260D6BCE35B033E68C0F33B03E857BDA601A33D8BCE013D01910A3CC26ECDBD45839FBB2A1067BC1D12153C7607E6BC178F57BCE6ED563D5A21413D38E08A3DF1A74F3D6FDBBBBDEDC878BDDCD1A43D050681BCFE58383D2C8F913D3C458ABD |
|  2 | 0x1AA84F3C9125FEBC54DEA1BDC68C0ABCB624A9BD7FDF54BD4392513DA7F81A3D855FC33CE376F43CD73D67BDA65F1A3D4829C93DA8FA86BD08016FBC5D6676BCF02893BB9967C2BD302C8DBAF413A1BDF9A9E6BD68070DBD643E49BDF1ABB23C60E09EBCD8372EBD4F1AFDBC54CFD7BC2DC583BDD584CCBC2F105B3C211BE33DCD52D63DAD82813D42707D3C5A75D2BCD44C903CD1D1353D995D59BCC257FABC16E538BDB9BD7E3935653FBD9FDED53CA92C603DA2B726BCF9862C3DCE5B1CBDA5E926BCBB08393DE1AD36BCB6F277BC18A79FBDBBDEA53DF9BB6E3DAD84753DC04D2D3C26DD28BDA41FECBD771380BD09D5663D4A2D623D7D080E3CDD107A3DBA28CDBBC692F23C061E673DE4D62D3DC207E33B971566BDEEA7B4BD48092DBDBA01743C1B04BF3D7478DEBC0E5E9DBDBFBA7B3C51F2A3BC3A20043B52789E3B005B5D3CD5FBBA3C3DA4553C02D1AD3C8AD912BD51BDB2BDE51E7F3C588D56BB62EFA1BA8894023D1DF3203C781265BC986CD5BC2FC783BD25AE0ABC51B3A83CAD0B483A5E99A4BDCB26883D61BCAD3DF1FD3A3D354BB4BCE8A7883C3B6FF8BC460323BD9F92BB3DE240883D464B53BD70C7A5BCCB8F9BBD52831BBD06EACB3CB3043A3C203465BD52CF163E0CB903BDE6E614BDB6AC783D390181BD0534153DAB0001BDD4D8E73B2114943CF819D9BBBB6512BD54CCD5BB929342BD7C3FCA3D7CB7F6BCF4C7B7BAEA7E69BD9E2FFB3BC0A409B99FBB7E3D2A7435BC968896BC742290BDCF057E3CD31E263D658FD1BAFC1AFA3B888E303C3DB10C3D06822CBD034E82BDF16A963C73D81F3DDD5875BCDB1986BDC0B1DABA4E2B2A3B0C87233D88770A3E91BF343DA1B3C5BD2D337BBD9B6CF83C99AE773CEAF6D03C73512DBD9AE4E2BD4FAB5FBD7DA61EBDC4D0A83DE71198BD2E01C9BCCC19483C08B729BD97404B3DFFEE72BBD4E47B3DBF3AD0BB433498BD80EC353D7B0CBEBD8E3CF8BCF9218DBDB2C324BDDCF8C33B630394BD8D269F3D0BC6033DBA766F3DC9EA033DB55A0E3DD66505BD9C939BBDF06AAC3C1253CF3D45CC093D461751BC0C0CCD3D9015AA3DD2DF7EBC2B444A3BFF2BA5BDC6B3F03C66CC1F3D48876C3D703203BD2C0B253C1DDCDB3D0B44EFBD88F0D83D6B30D0BCC5FC863C7F4595BC51ABE5BCFFEC79BDDAB01ABDF93587BDB40EFC3D1E2F16BD1AA2A5BBBA39BA3C3556D13CEA68383D2E49C83CCD1CA6BDF6A2383C18E15B3DBABB8C3CFFE942BD1729320AAB1E2CBD4FE003BCC67F9BBD9F43503D0F4681BB3313D73C72D8F43ACCF656BC4C81393D1F2C1D3D6F5A81BB5510E03DF5672F3D8E73CCBB79A2F7BC329F43BCE017B13C2328AFBD567DA83A4A7C113DA23829BC1FC09C3DB0E6E5BCA811DD3CC68EDF3CDB2B0D3D5A90263BE743ACBD59D3893C99C1153D02760EBD365659BD9BCFB63CF007BF3DC3D911BD74FD4CBD01757C3CF05949BD0F427C3D4BE18BBDD7420FBD0CAF24BC9D5DC2BA6DC4BF3C26DD893CD94DB83D47330CBD5ECA3A3D82EA8E3C434568BC0DC178BD2C14E63B7E4F9F3DD39383BD566A403C4799B9BC698BDABCC20DD6BBAE7499BDBD252ABDA6ED873DF6EAA3BD61FC7FBDC87F7D3D241EB5BC70BCB93D0842613CEAA82DBB55AB423CE9DE133D43E51E3C041C75BCEECA033D8EFD4F3919A45CBD1041F13D6C18963AC1FCD33D6C3E843D57E72D3C4137933ADB1DB2BBC07A8D3C5EA5233DAC3AAA3C2C03A0BD3DF1AE3B7233913D522B043C876C06BCE842713D97E41FBD972203BE1367043DF46D6BBDBD2E038AE450483D3B2DAABCCC3F203C9E2EF2BC4D687C3D85659CBD4028BF3C750AEA3D5C57413DDE1BC7BCC9E24C3DB664C7BDDBC2BF39AFC6293C847CB23C1D4A063D96DD38BC94CE263CF8757B3DD42907BDBF7BDB3DB575983C637418BCA3F180BD2EB1B83D038F953B331ECC3B68189A3D06A5813D5F0C55BD1DC69CBB21F8353C8CAA1EBDD78607BDEDA683BBE712A4BD1B17423DF3600FBD44DA09BD53A8993CE1D3E73D0E75F23DAC9B173D3F0FF93ADDF5CDBC91D4023D80C3943C7DC3AE3C332F533D521A3DBD364ECDBDC0E06ABC944C753D281A97BA41E78EBD1A373FBCCBD5123D996AE2BD4BDD413DD6A05CBD9DA293BCEA48903BE42F313D88F50ABC |
+----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

PREV HOME UP NEXT