When I run SELECT query in gfsh console, it works as expected:
query --query="SELECT * FROM /channelProfiles_composite_asrd WHERE profile.channelCode='JJ'"
But similar DELETE query fails:
query --query="DELETE * FROM /channelProfiles_composite_asrd WHERE profile.channelCode='JJ'"
Result : false
startCount : 0
endCount : 20
Message : Query is invalid due for error : <Syntax error in query: unexpected token: FROM>
NEXT_STEP_NAME : END
Does gemfire support DELETE?
Geode / GemFire OQL unfortunately doesn't support DELETE. You would have to iterate over your result set and delete 'manually'.
From gfsh prompt you can use remove commands as below
removing specific keys
data with String keys
remove --region=RegionName --key=abc
data with Other object keys must use the key-class as well as below
remove --region=RegionName --key-class=java.lang.Integer --key=1
For a large number of records to be deleted, what I do is search the list of keys to be deleted using Select query. then build a scripts of above mentioned Remove commands and run them together.
For removing all region data
remove --region=RegionName --all
Otherwise you will need to a Java program to delete the region data using Gemfire API
Here is a GemFire function I wrote that will clear out a region. This code could be a lot shorter if you threw out the fluff and extra features we never ended up using. Also in hindsight I didn't need to "synchronize" the list of regions being cleared because the odds of two people calling the function in the same microsecond are virtually zero.
We use it in both GemFire 7 and GemFire 8 clusters. Once you put this in a jar and install it, you can call this function from gfsh to clear a region.
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
import java.util.Set;
import com.gemstone.gemfire.LogWriter;
import com.gemstone.gemfire.cache.CacheFactory;
import com.gemstone.gemfire.cache.Declarable;
import com.gemstone.gemfire.cache.EntryNotFoundException;
import com.gemstone.gemfire.cache.Region;
import com.gemstone.gemfire.cache.execute.Function;
import com.gemstone.gemfire.cache.execute.FunctionContext;
import com.gemstone.gemfire.cache.execute.RegionFunctionContext;
import com.gemstone.gemfire.cache.partition.PartitionRegionHelper;
import com.gemstone.gemfire.distributed.DistributedSystem;
public class ClearRegionFunction implements Function, Declarable {
private static final long serialVersionUID = 1L;
private static LogWriter log;
private static List<String> clearingRegionList = new ArrayList<String>();
static {
DistributedSystem ds = CacheFactory.getAnyInstance().getDistributedSystem();
log = ds.getLogWriter();
}
@Override
public void execute(FunctionContext fc) {
RegionFunctionContext rfc = (RegionFunctionContext) fc;
Region region = rfc.getDataSet();
String regionName = region.getName();
//If passed a flag of "true", that says to simulate the clear, but don't actually clear.
//This is used to test if a clear is already in progress, in which case we'd return false.
Boolean simulate = (Boolean)rfc.getArguments();
log.fine("Argument passed = " + simulate);
if (simulate == null) simulate = false;
if (simulate) {
rfc.getResultSender().lastResult( ! clearingRegionList.contains(regionName));
return;
}
log.warning("Clearing region: " + regionName); // Used "warning" because clearing a region is serious.
try {
// Protect against the same region being cleared twice at the same time.
synchronized (clearingRegionList) {
if (clearingRegionList.contains(regionName)) {
log.error("Clear of region " + regionName + " is already in progress. Aborting.");
// Let the client know we ignored their "clear" request.
rfc.getResultSender().lastResult(false);
return;
}
clearingRegionList.add(regionName);
}
if (!PartitionRegionHelper.isPartitionedRegion(region)) {
region.clear();
rfc.getResultSender().lastResult(true);
} else {
// We are going to clear the region in a partitioned manner, each node only clearing
// the data in it's own node. So we need to get the "local" region for the node.
Region localRegion = PartitionRegionHelper.getLocalDataForContext(rfc);
// Beware, this keySet() is a reference to the actual LIVE key set in memory. So
// we need to clone the set of keys we want to delete, otherwise we'll be looping
// through a live list and potentially deleting items that were added after the
// delete started.
List keyList = new ArrayList(localRegion.keySet());
// Once we have the keys, go ahead and set the lastResult to "true" to
// unblock the caller, because this could take a while. (The caller doesn't actually
// unblock until ALL nodes have returned "true".)
rfc.getResultSender().lastResult(true);
int regionSize = keyList.size();
log.info("Region " + regionName + " has " + regionSize + " entries to clear.");
int count = 0;
for (Object key : keyList) {
//The "remove" method returns the object removed. This is bad because it (sometimes?) causes
//GemFire to try and deserialize the object, and that fails because we don't have the class on
//our server classpath. But if we invalidate first, it destroys the entry object without
//deserializing it. Then "remove" cleans up the key.
try {
localRegion.invalidate(key);
localRegion.remove(key);
} catch (EntryNotFoundException enfe) { //If the entry has disappeared (or expired) by the time we try to remove it,
//then the GemFire API will throw an exception. But this is okay.
log.warning("Entry not found for key = " + key.toString(), enfe);
}
count++;
// Every 10000 is frequent enough to give you a quick pulse, but
// not so frequent as to spam your log.
if (count % 10000 == 0) {
log.info("Cleared " + count + "/" + regionSize + " entries for region " + regionName);
}
}
}
log.warning("Region cleared: " + regionName);
synchronized (clearingRegionList) {
clearingRegionList.remove(regionName);
}
} catch (RuntimeException rex) {
// Make sure we clean up our tracking list even in the unlikely event of a blowup.
clearingRegionList.remove(regionName);
log.error(rex.toString(), rex); // Log AND throw is bad, but from my experience, a RunTimeException
// CAN get sent all the way back to the client and never show
// up in gemfire.log. (If the exception happens before last result)
throw rex;
}
}
@Override
public String getId() {
return "clear-region-function";
}
@Override
public void init(Properties arg0) { }
@Override
public boolean hasResult() { return true; }
@Override
public boolean isHA() { return true; }
@Override
public boolean optimizeForWrite() {return true;}
}
We use GemFire, and I ended up writing a Function to wipe out an entire region. This executes much faster than a simple client-side loop deleting entries one at a time, because the Function is distributed, and each node only clears the entries local to that node.
And Functions can be executed from gfsh, so it's pretty easy to use.
I could share the source code for this Function if that would be of use?
You need to use GemFire function to clear region , its very fast and optimize way to delete records from gemfire regions ,find detail code on following github repo
POM:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>GemFireRemoveAllDataFunction</groupId>
<artifactId>GemFireRemoveAllDataFunction</artifactId>
<version>0.0.1-SNAPSHOT</version>
<build>
<sourceDirectory>src</sourceDirectory>
<plugins>
<plugin>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.7.0</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>io.pivotal.gemfire</groupId>
<artifactId>geode-core</artifactId>
<version>9.6.0</version>
</dependency>
</dependencies>
</project>
Function :
package com.khan.viquar.gemfire;
import java.util.ArrayList;
import java.util.List;
import org.apache.geode.cache.Declarable;
import org.apache.geode.cache.Region;
import org.apache.geode.cache.execute.Function;
import org.apache.geode.cache.execute.FunctionContext;
import org.apache.geode.cache.execute.RegionFunctionContext;
import org.apache.geode.cache.partition.PartitionRegionHelper;
@SuppressWarnings("rawtypes")
public class ClearRegionRemoveAllDataFunction implements Function, Declarable {
private static final long serialVersionUID = 11L;
private static final int batchSize = 30000;
@SuppressWarnings("unchecked")
public void execute(final FunctionContext ctx) {
if (ctx instanceof RegionFunctionContext) {
final RegionFunctionContext rfc = (RegionFunctionContext) ctx;
try {
final Region<Object, Object> region = rfc.getDataSet();
if (PartitionRegionHelper.isPartitionedRegion(region)) {
clear(PartitionRegionHelper.getLocalDataForContext(rfc));
} else {
clear(region);
}
ctx.getResultSender().lastResult("Success");
} catch (final Throwable t) {
rfc.getResultSender().sendException(t);
}
} else {
ctx.getResultSender().lastResult("ERROR: The function must be executed on region!");
}
}
private void clear(final Region<Object, Object> localRegion) {
int numLocalEntries = localRegion.keySet().size();
if (numLocalEntries <= batchSize) {
localRegion.removeAll(localRegion.keySet());
} else {
final List<Object> buffer = new ArrayList<Object>(batchSize);
int count = 0;
for (final Object k : localRegion.keySet()) {
buffer.add(k);
count++;
if (count == batchSize) {
localRegion.removeAll(buffer);
buffer.clear();
count = 0;
} else {
continue;
}
}
localRegion.removeAll(buffer);
}
}
public boolean hasResult() {
return true;
}
public String getId() {
return ClearRegionRemoveAllFunction.class.getSimpleName();
}
public boolean optimizeForWrite() {
return true;
}
public boolean isHA() {
return true;
}
}
