Hi, Here's an example how to implement this using sequences and SQL functions.
Please note how I refer to node name here - most likely you'll have to tweak this thing. - Alex 2017-09-18 16:17 GMT+03:00 Alexander Paschenko <[email protected]>: > Hello, > > Andrey, I believe you're wrong. INSERT from SELECT should work. AUTO > INCREMENT columns indeed are not supported for now though, it's true. > > - Alex > > 2017-09-18 16:09 GMT+03:00 Andrey Mashenkov <[email protected]>: >> Hi, >> >> Auto-increment fields are not supported yet. Here is a ticket for this [1] >> and you can track it's state. >> Moreover, underlying H2 doesn't support SELECT with JOINs nested into >> INSERT\UPDATE query. >> >> [1] https://issues.apache.org/jira/browse/IGNITE-5625 >> >> On Mon, Sep 18, 2017 at 12:31 PM, acet <[email protected]> wrote: >>> >>> Hello, >>> I would like to insert the result of a select query into a cache in >>> ignite. >>> Something like: >>> >>> INSERT INTO "new_cache_name".NewCacheDataType(ID, CUSTOMERID, PRODUCTNAME) >>> (SELECT {?}, c.id, p.product_name >>> FROM "customers".CUSTOMER as c >>> JOIN "products".PRODUCT as p >>> ON c.id = p.customer_id) >>> >>> in the place of the {?} i would like to put in something similar to >>> AtomicSequence, however seeing as this will be work done without using the >>> client I cannot tell how this is possible. >>> Can someone advise if this can be done, and if so, how? >>> >>> Thanks. >>> >>> >>> >>> -- >>> Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >> >> >> >> >> -- >> Best regards, >> Andrey V. Mashenkov
/* * Licensed to the Apache Software Foundation (ASF) under one or more * contributor license agreements. See the NOTICE file distributed with * this work for additional information regarding copyright ownership. * The ASF licenses this file to You under the Apache License, Version 2.0 * (the "License"); you may not use this file except in compliance with * the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package org.apache.ignite.internal.processors.cache; import java.util.Arrays; import java.util.Collection; import java.util.Collections; import java.util.List; import javax.cache.Cache; import org.apache.ignite.Ignite; import org.apache.ignite.IgniteCache; import org.apache.ignite.Ignition; import org.apache.ignite.cache.query.Query; import org.apache.ignite.cache.query.QueryCursor; import org.apache.ignite.cache.query.SqlFieldsQuery; import org.apache.ignite.cache.query.SqlQuery; import org.apache.ignite.cache.query.annotations.QuerySqlField; import org.apache.ignite.cache.query.annotations.QuerySqlFunction; import org.apache.ignite.configuration.CacheConfiguration; import org.apache.ignite.configuration.IgniteConfiguration; import org.apache.ignite.internal.IgniteInternalFuture; import org.apache.ignite.internal.processors.query.GridQueryProcessor; import org.apache.ignite.internal.processors.query.GridRunningQueryInfo; import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi; import org.apache.ignite.spi.discovery.tcp.ipfinder.TcpDiscoveryIpFinder; import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder; import org.apache.ignite.testframework.junits.common.GridCommonAbstractTest; /** * */ public class SequenceTest extends GridCommonAbstractTest { /** */ private static final TcpDiscoveryIpFinder ipFinder = new TcpDiscoveryVmIpFinder(true); /** * Grid name to refer in funct */ private static String gridName = null; /** {@inheritDoc} */ @Override protected IgniteConfiguration getConfiguration(String igniteInstanceName) throws Exception { IgniteConfiguration cfg = super.getConfiguration(igniteInstanceName); if ("client".equals(cfg.getIgniteInstanceName())) cfg.setClientMode(true); ((TcpDiscoverySpi)cfg.getDiscoverySpi()).setIpFinder(ipFinder); CacheConfiguration<Integer, Integer> cc = new CacheConfiguration<>(DEFAULT_CACHE_NAME); cc.setCopyOnRead(true); cc.setIndexedTypes(Integer.class, Integer.class); cc.setSqlFunctionClasses(TestSQLFunctions.class); cfg.setCacheConfiguration(cc); return cfg; } /** {@inheritDoc} */ @Override protected void beforeTestsStarted() throws Exception { super.beforeTestsStarted(); startGridsMultiThreaded(1); gridName = getTestIgniteInstanceName(0); } /** {@inheritDoc} */ @Override protected void afterTestsStopped() throws Exception { super.afterTestsStopped(); stopAllGrids(); } /** * Test collecting info about running. * * @throws Exception If failed. */ public void testNext() throws Exception { IgniteCache<Integer, Integer> cache = grid(0).cache(DEFAULT_CACHE_NAME); List<List<?>> res = cache.query(new SqlFieldsQuery("select next('s', 0), next('s', 0), next('s', 0)") .setLocal(true)).getAll(); assertEqualsCollections(Collections.singletonList(Arrays.asList(1L, 2L, 3L)), res); } /** * Utility class with custom SQL functions. */ public static class TestSQLFunctions { /** * @param seqName Sequence name. * @param initVal Initial sequence value (will be ignored for all calls but the very first). * @return Next value for sequence w/given name. */ @QuerySqlFunction public static long next(String seqName, long initVal) { return Ignition.ignite(gridName).atomicSequence(seqName, initVal, true).incrementAndGet(); } } }
